搜索特定的字符串并在垂直之间删除所有单元格

Question

我想创建一个 VBA 函数来搜索术语 red 并删除 Red 之间的所有剩余单元格空单元格。正如您在照片栏中看到的，c 代表期望的结果。我下面的代码现在以垂直方式删除单元格之间的所有空白空间。我只需要将红色部分的搜索添加到此代码中即可。

Sub collapse_columns()
    Dim x As Integer
    For x = 1 To 4
        collapse_column x
    Next
End Sub


Sub collapse_column(column_number As Integer)

    Dim row As Long
    Dim s As Worksheet
    Dim last_row As Long
    Set s = ActiveSheet ' work on the active sheet
    'Set s = Worksheets("Sheet1") 'work on a specific sheet
    
    last_row = ActiveSheet.Cells(s.Rows.Count, column_number).End(xlUp).row
    
    For row = last_row To 1 Step -1
      If Cells(row, column_number).Value = "" Then Cells(row, column_number).Delete xlUp
    Next

End Sub

Answer 1

使用自动过滤器，您可以避免循环和逐行删除。

    Application.DisplayAlerts = False
    With ActiveSheet
        .Rows(1).EntireRow.Insert 'If you have headers you don't need
        .Cells(1, 1).Value = "Temp" 'If you have headers you don't need
        .Cells(1, 1).AutoFilter 1, "<>red"
        
        'If you have headers start on row 2
        .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).Delete
        If .FilterMode Then
            .ShowAllData
        End If
    End With
    Application.DisplayAlerts = True

如果您只想修改现有代码，请将此行：更改

If Cells(row, column_number).Value = "" Then Cells(row, column_number).Delete xlUp

为：

If Not Cells(row, column_number).Value Like "red" Then Cells(row, column_number).Delete xlUp