View Full Version : [SOLVED:] Delete row with condition
Tom Jones
10-05-2015, 08:37 AM
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
10-05-2015, 11:58 AM
?
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
10-05-2015, 12:11 PM
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
10-05-2015, 03:39 PM
welcome.
i corrected the code in my previous message.
Tom Jones
10-05-2015, 11:43 PM
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
10-06-2015, 12:26 AM
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
10-06-2015, 12:34 AM
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
10-06-2015, 01:06 AM
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
10-06-2015, 06:37 AM
you are welcome.
please mark the thread as solved from thread tools for future references...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.