从工作表中删除空行和嵌入的字段名称

该示例可搜索一列数据的内容。如果单元格为空或者包含一个特定的单元格值（在此示例中为“Hello”），则代码就会删除该行，然后移到下一行进行检查。

Sub CleanUp()
    On Error Resume Next

    With ActiveSheet
        'Change the column value to suit your needs.
        LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
        Set Rng1 = .Range(Cells(1, "A"), Cells(LastRw, "A"))
        Set Rng2 = .Range(Cells(2, "A"), Cells(LastRw, "A"))
    End With

    With Rng1
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .AutoFilter Field:=1, Criteria1:="Hello"
        Rng2.SpecialCells(xlCellTypeVisible).EntireRow.Delete
       .AutoFilter
End With
 End Sub 

创建数据的主列表

该代码通过将工作表中的信息拼凑在一起来创建一个主列表。此示例创建了一个“Master”工作表，搜索列直到遇到一个空单元格，再将扫描数据复制到该 Master 工作表中，然后继续搜索下一个空单元格。

Sub CopyData()
    Dim i As Long, rng As Range, sh As Worksheet
    'Change these worksheet names as needed.
    Worksheets.Add(After:=Worksheets( _
       Worksheets.Count)).Name = "Master"
    Set sh = Worksheets("Input-Sales")
    i = 1
    Do While Not IsEmpty(sh.Cells(i, 1))
        Set rng = Union(sh.Cells(i, 1), _
           sh.Cells(i + 2, 1).Resize(3, 1))
        rng.EntireRow.Copy Destination:= _
           Worksheets("Master").Cells(Rows.Count, 1).End(xlUp)
        i = i + 16
    Loop
End Sub 

      
      (责任编辑：admin)