Delete Rows IF criteria - Need VBA Help

[unit213]

I need to delete rows if there are blank cells in column A. I also need conditional formatting removed from those rows. Does anyone know a VBA solution?

TIA,

Dan

 

[kskinne]

do you mean you want to clear the contents of the row where cell A is blank, or do you mean completely delete the row so that for example row 5 becomes row 4 when 4 is deleted? if you mean clear contents, this will work

Sub test()
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row
For i = 1 To LastRow
If Cells(i, 1) = "" Then
    With Rows(i & ":" & i).EntireRow
        .ClearContents
        .FormatConditions.Delete
    End With
End If
Next i
End Sub

otherwise, to delete the row use this:

Sub test()
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, 1) = "" Then Rows(i & ":" & i).EntireRow.Delete
Next i
End Sub

if you use the second code, there would be no need to delete the conditional formatting in a row that no longer exists


hth
kevin

 

[giveme_a_name]

Very useful for me too! How could I add to this code to delete rows IF cell in Column A does NOT include a given character, in this case "@" (ie. first delete rows where cells in Column A are blank, then delete those where "@" is missing?

Am tempted to use Worksheet.Function.Insnumber method [If(Isnumber(Search("@".. )] is what I use in Excel to perform this operation) but cannot get synthax right.

Any help hugely appreciated!

olivier