Deleting Blocks of Empty Rows

[Guest]

I would like some help in deleting a block of rows that have blank values in
columns B and C. I have code that will loop through each of the cells,
however this is time consuming with large worksheets.
The code snippet below gives me the count of rows I want to keep and the
count of rows I want to delete. My thought was to store the counts into the
two variables (cntrMTNo,cntrMTYes) and try and pass the variable values into
a range or row statement for deleting. Any help would be greatly
appreciated. Thanks


*************************************************************
Dim cntrMTNo As Integer
Dim cntrMTYes As Integer

Range("A7").Select
Do Until ActiveCell.Offset(0, 1) = "TOTAL:"
If Not IsEmpty(ActiveCell.Offset(0, 1)) And Not
IsEmpty(ActiveCell.Offset(0, 2)) Then
cntrMTNo = cntrMTNo + 1
End If
ActiveCell.Offset(1, 0).Select
Loop

Range("A7").Select
Do Until ActiveCell.Offset(0, 1) = "TOTAL:"
If IsEmpty(ActiveCell.Offset(0, 1)) And IsEmpty(ActiveCell.Offset(0, 2))
Then
cntrMTYes = cntrMTYes + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
************************************************************

'Rows("11:37").Select
'Selection.Delete Shift:=xlUp

 

[Guest]

AXLA,

 

[Bernie Deitrick]

Axla,

It's best to use built-in Excel functionality. In this case, Autofilter.
Cose assumes that your data table at least covers A7, and has one row of
headers.

With Range("A7").CurrentRegion
.AutoFilter Field:=2, Criteria1:="="
.AutoFilter Field:=3, Criteria1:="="
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP

 

[jordanctc]

I do something similar to *Selection.Delete Shift:=xlUp* and found tha
turning off screenupdating improves performance. It is prett
dramatic, like 5-10 times faster. But maybe someone knows a mor
efficent method than this.

Jorda

 

[Guest]

axla,

I have benchmarked many different methods on my pc for doing exactly what
you are trying to do. Nothing ran faster than the following method:

Loop through your data and mark the rows for deletion in some way, then sort
the data so that all of your target rows are together in one cluster
(preferably at the top or the bottom of the sheet), then delete this cluster
all at once.

FYI, I tried the following methods, but they all proved to be considerably
slower with large amounts of data (even though they may consume much less
code):

1. Copy the row numbers to be deleted into an array, then step backward
through the array and delete the rows;

2. Filter on the data to be deleted and then delete the visible cells;

3. Step backward through the sheet and immediately delete each row meeting
the deletion criteria;

Good luck. Hope this helps.

 

[Guest]

Bernie,

Thank you very much. I just need to figure out how it works:]. Another
question, How come the statement
"Rows("11:20") can't be replaced with
"Rows("var1:var2") where var1 and var2 are integers and
var1 = 11 and var2 = 20? Thanks again for your help.

AxLa

 

[Bernie Deitrick]

AxLa,

You need to treat them as variables:

Rows(var1 & ":" & var2)

HTH,
Bernie
MS Excel MVP

Bernie,

Thank you very much. I just need to figure out how it works:]. Another
question, How come the statement
"Rows("11:20") can't be replaced with
"Rows("var1:var2") where var1 and var2 are integers and
var1 = 11 and var2 = 20? Thanks again for your help.

AxLa

Axla,

It's best to use built-in Excel functionality. In this case, Autofilter.
Cose assumes that your data table at least covers A7, and has one row of
headers.

With Range("A7").CurrentRegion
.AutoFilter Field:=2, Criteria1:="="
.AutoFilter Field:=3, Criteria1:="="
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP

values
in into
the values
into IsEmpty(ActiveCell.Offset(0,
2))

 

[Bernie Deitrick]

Quartz,

I agree with you about sorting prior to deletion being the fastest method
for large data blocks. But the OP had a little snippet of code referring to
rows 11 to 37, so it is unlikely to make much difference, however the
deletion is done.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Agreed.

However, OP also mentioned the process as being time consuming for large
sheets...I was assuming OP was looking for a resolution under other scenarios
as well.

 

[Guest]

Jordan,

Thanks for the suggestion. I'm trying to test it now to see what kind of
performance increase I can get.

Axla

 

[Guest]

Bernie,

Thanks again. I got your solution to work for one type of sheet but it
failed on another so I'll have to look at it to understand why it fails.
Listed below is the code I implemented since I had to roll this out today.

Application.ScreenUpdating = False
Range("A28").Select
Do Until ActiveCell.Offset(0, 1) = "TOTAL:"
If IsEmpty(ActiveCell.Offset(0, 1)) And IsEmpty(ActiveCell.Offset(0,
2)) Then
cntr = cntr + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
If cntr = 0 Then
Range("c5").Select
Exit Sub
End If
'******************************************************
Range("A28").Select
Do Until IsEmpty(ActiveCell.Offset(0, 1)) And
IsEmpty(ActiveCell.Offset(0, 2))
ActiveCell.Offset(1, 0).Select
Loop
begAddr = ActiveCell.Address
begAddr = Val(Mid(begAddr, 4, 100))
'******************************************************
Range("A28").Select
Do Until ActiveCell.Offset(1, 1) = "TOTAL:"
ActiveCell.Offset(1, 0).Select
Loop
endaddr = ActiveCell.Address
endaddr = Val(Mid(endaddr, 4, 100))
'******************************************************
Rows(begAddr & ":" & endaddr).Delete
Application.ScreenUpdating = True
Range("c5").Select

The screen updating makes a huge difference in performance. Thanks again.

 

[Guest]

Quartz,

Thanks for the advice. When I get a little more time I'll try your
solution. Listed below is what I ended up implementing.

Application.ScreenUpdating = False
Range("A28").Select
Do Until ActiveCell.Offset(0, 1) = "TOTAL:"
If IsEmpty(ActiveCell.Offset(0, 1)) And IsEmpty(ActiveCell.Offset(0,
2)) Then
cntr = cntr + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
If cntr = 0 Then
Range("c5").Select
Exit Sub
End If
'******************************************************
Range("A28").Select
Do Until IsEmpty(ActiveCell.Offset(0, 1)) And
IsEmpty(ActiveCell.Offset(0, 2))
ActiveCell.Offset(1, 0).Select
Loop
begAddr = ActiveCell.Address
begAddr = Val(Mid(begAddr, 4, 100))
'******************************************************
Range("A28").Select
Do Until ActiveCell.Offset(1, 1) = "TOTAL:"
ActiveCell.Offset(1, 0).Select
Loop
endaddr = ActiveCell.Address
endaddr = Val(Mid(endaddr, 4, 100))
'******************************************************
Rows(begAddr & ":" & endaddr).Delete
Application.ScreenUpdating = True
Range("c5").Select

As I mentioned to Bernie, the screen updating makes a huge difference in
performance. Thanks Again.

AxLa