使用VBA在Excel中删除行
问题描述:
我是VBA的新程序员。我正在使用一条简单的线删除我制作的for循环中的行。这个简单的行删除当前行。使用VBA在Excel中删除行
Rows(i).EntireRow.Delete
但是,我有D列中的内容,我不想删除。有没有办法将“EntireRow”证明为“A,B,C”之类的东西?
a b c d
1 x x x N
2 x x x N
3 x x x N
X =不需要。 N =需要。
答
如果您录制宏,你喜欢的东西:
Selection.Delete Shift:=xlUp
所以这里有几个选项:
Rows(i).Resize(, 3).Delete xlUp
Range("A:C").Rows(i).Delete xlUp
Range("A1:C1").Offset(i - 1).Delete xlUp
答
试试这个VBA代码:
Sub Delete_Row_If_Not_Whatever()
Dim RowToTest As Long
'Macro deletes entire row if "Whatever" is not found in column "D"
Application.ScreenUpdating = False 'Turn off screen updating. Code runs faster without screen flicker
'Column Count 4 = "D"
For RowToTest = Cells(Rows.Count, 4).End(xlUp).row To 2 Step -1
'Look for "Whatever" save row and delete other rows. Replace "Whatever" with what you want to keep.
'Column Count 4 = "D"
With Cells(RowToTest, 4)
If .Value <> "Whatever" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
Application.ScreenUpdating = True 'Turn on screen updating
End Sub
一定要保存在开始之前进行备份。 :)
'Range(“A”&i&“:C”&i)' –