Delete row with condition

[Tom Jones]

Hello,
I need a VBA code to delete the whole row if it is satisfied that all columns B: K is empty.
If at least one of the columns is not empty, that row will not wash off. The data is from A2: K98500. In A1: K1 header
Thank you.

[mancubus]

?


Sub vbax_53918_DeleteBlankRows()
Dim i As Long

With Worksheets("MySheet") 'Change MySheet to Suit
For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
If Application.CountA(.Range("B" & i & ":K" & i) = 0 Then .Rows(i).Delete
Next i
End With
End Sub

[Tom Jones]

Thanks for replay mancubus,

Column A has no empty cell, empty cell could be in column B:K. I need to delete row and shiftUp cells, only if all cells from column B to K, is empty.
Can you fix this?

[mancubus]

welcome.
i corrected the code in my previous message.

[Tom Jones]

Thanks mancubus for VBA code, but for some reason the code is not working. Does not display any error nor delete rows.



Sub vbax_53918_DeleteBlankRows()
Dim i As Long

With Worksheets("Sheet1") 'Change MySheet to Suit
For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
If Application.CountA(.Range("B" & i & ":K" & i)) = 0 Then .Rows(i).Delete
Next i
End With
End Sub

[mancubus]

it seems you have added the missing paranthesis.

try attached file. same code worked for sample file.



ps: check cells for non printing characters..

[mancubus]

with autofilter method...



Sub vbax_53918_DeleteBlankRows_AutoFilter()

With Worksheets("MySheet") 'Change MySheet to Suit
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=2, Criteria1:="="
.Cells(1).AutoFilter Field:=3, Criteria1:="="
.Cells(1).AutoFilter Field:=4, Criteria1:="="
.Cells(1).AutoFilter Field:=5, Criteria1:="="
.Cells(1).AutoFilter Field:=6, Criteria1:="="
.Cells(1).AutoFilter Field:=7, Criteria1:="="
.Cells(1).AutoFilter Field:=8, Criteria1:="="
.Cells(1).AutoFilter Field:=9, Criteria1:="="
.Cells(1).AutoFilter Field:=10, Criteria1:="="
.Cells(1).AutoFilter Field:=11, Criteria1:="="
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

[Tom Jones]

You're right mancubus.
My file was exported out of a database and have some special characters. When I copied my data in your file has not worked VBA code. I cleaned the file and now is perfect.

Thank you very much.

[mancubus]

you are welcome.
please mark the thread as solved from thread tools for future references...