我想創建一個 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
uj5u.com熱心網友回復：
使用自動過濾器，您可以避免逐行回圈和洗掉行。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/456328.html