Thursday, February 3, 2011

Find a value in cell and Delete the entire column

If you want to delete a row when the cell value = del in column 1 or A in a sheet

Here is the code --------

change the sheet index and range according to your requirement

Sub find_delete()

Application.ScreenUpdating = False
Dim a As Range

Dim SrchRnga

Set SrchRnga = Sheets(1).Range("a1: a" & ActiveSheet.UsedRange.Rows.Count)

Set a = SrchRnga.Find("del", LookIn:=xlValues)
If Not a Is Nothing Then a.EntireRow.Delete
Loop While Not a Is Nothing
Application.ScreenUpdating = True
End Sub

Mehtod 2

change the sheet index and column number according to your requirement

Sub find_delete1()
Application.ScreenUpdating = False

Dim i As Long

For i = Sheets(1).UsedRange.Rows.Count To 1 Step -1

If Sheets(1).Cells(i, 1).Value = "del" Then

Sheets(1).Cells(i, 1).EntireRow.Delete

End If
Application.ScreenUpdating = True


End Sub

No comments:

Post a Comment