Excel 表格数据处理 VBA 代码

Sub ExcelOperations() Dim ws As Worksheet Dim lastRow As Long Dim rng As Range Dim cell As Range

'Set the worksheet
Set ws = ThisWorkbook.Worksheets('Sheet1')

'Delete columns
ws.Columns('C').Delete
ws.Columns('D').Delete
ws.Columns('F').Delete
ws.Columns('H').Delete
ws.Columns('K').Delete

'Move '产品图号' column after '产品名称' column
ws.Columns('H').Cut
ws.Columns('B').Insert Shift:=xlToRight

'Sort the table based on '获得码' column
lastRow = ws.Cells(ws.Rows.Count, 'A').End(xlUp).Row
Set rng = ws.Range('A2:K' & lastRow)
rng.Sort Key1:=ws.Range('G2:G' & lastRow), Order1:=xlAscending, Header:=xlYes

'Delete rows containing '外购件'
For Each cell In rng.Columns('F').Cells
    If cell.Value Like '*外购件*' Then
        cell.EntireRow.Delete
    End If
Next cell

'Delete rows with '重量' column value of 0
For Each cell In rng.Columns('E').Cells
    If cell.Value = 0 Then
        cell.EntireRow.Delete
    End If
Next cell

'Delete '获得码' column
ws.Columns('G').Delete

'Find and highlight duplicate materials
For Each cell In rng.Columns('C').Cells
    If WorksheetFunction.CountIf(rng.Columns('C'), cell.Value) > 1 Then
        cell.Interior.Color = RGB(255, 0, 0) 'Red color
    End If
Next cell

'Fill '总重' in cell F2
ws.Range('F2').Value = '总重'

End Sub