Detele emply rows from the top or from the bottom ?

[garyc]

This question was raised by someone in this morning. And I have tried it and have some problem. The successful module and my failure module are shown below. I just wanna know why my module can't get the same result as another one. Thanks!

This is done by one of the experts in this forum
Sub DeleteEmptyRows()

lastrow = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count
lastrow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub
This is done by me
Sub delemptyrow1()

Range(Range("a1"), Range("a65536").End(xlUp)).Select

numRows = Selection.Rows.Count
For i = 1 To Selection.Rows.Count
If Range("a1").Offset(i - 1, 0) = "" Then
Range("a1").Offset(i - 1, 0).EntireRow.Delete
End If

Next i

End Sub

The Question:
1) I dunno why my code can't finish the work at 1 times. I have to run it a few times to delete all the empty rows. ? It must be done from the bottom? But why, I dun get it ?

2) I dunno understand this line of code below in the successful module (at the top) ?:think:
lastrow = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.row = ???

[GTO]

This question was raised by someone in this morning. And I have tried it and have some problem. The successful module and my failure module are shown below. I just wanna know why my module can't get the same result as another one. Thanks!

This is done by one of the experts in this forum

Sub DeleteEmptyRows()

lastrow = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count
lastrow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

In the above, there is an issue in that lastrow is set to a value, then reset, nullifying the first value that it was set to. So...either you copied it a bit wrong, or one of those lines is unnecessary. I didn't check well, but I suspect the second would not work all the time, but I think the first one would...


This is done by me
Sub delemptyrow1()

Range(Range("a1"), Range("a65536").End(xlUp)).Select

numRows = Selection.Rows.Count
For i = 1 To Selection.Rows.Count
If Range("a1").Offset(i - 1, 0) = "" Then
Range("a1").Offset(i - 1, 0).EntireRow.Delete
End If

Next i

End Sub

The Question:
1) I dunno why my code can't finish the work at 1 times. I have to run it a few times to delete all the empty rows. ? It must be done from the bottom? But why, I dun get it ?

I'm not sure what you mean by it must be done from the bottom, as you are doing it from the top. As to having to repeat runing the macro to get rid of all the empty rows, watch what your loop is doing.

"i" increases by 1 ea loop. Let's say rows 3 and 4 are empty, and we're on the 4rth loop (i-1=3). Now since row three is empty, it is deleted...BUT now what was row four is now row three. Thus, next time we come around in the loop (i-1=4), we just passed right by the second blank row...

I hope that made sense? Actually, this now gets me to thinking about your comment as to must be done from bottom... If you do the stepping with negative steps, the above problem would be eliminated, as lets say that i-1 currently equals 4 and rows 3 and 4 are empty. Then row 4 is deleted, and next loop around, i-1=3, so row 3 is deleted also.


2) I dunno understand this line of code below in the successful module (at the top) ?:think:
lastrow = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.row = ???

ActiveSheet.UsedRange.Row returns the uppermost row in the used range.

Just as a suggestion, you might want to try this to better see what is happening with your code. Place some values in Col A, skipping a couple of rows here and there. Now reduce the vbide window til you can see the sheet (at least Col A) behind it. Then click in your sub and step thru the code by using the F8 key.

Anyways, hope this helps,

Mark

[Bob Phillips]

You have to delete bottom up otherwise you run the risk of missing a row if there are two or more consecutive rows that meet the delete criteria. Think of this situation, rows

4
5
6

You counter is pointing at row 4, which you delete. Row 5 then becomes row 4, but your pointer still gets incremented in the loop; thenext time around, the row processed is row 5, this row 5 was row 6, so the original row 5 has been completely missed.

[garyc]

Thanks Mark ~ I understand now

[mdmackillop]

The same applies to inserting rows. Try these

Sub ins1()
For i = 6 To 1 Step -1
Rows(i).Insert
Next
End Sub
Sub ins2()
For i = 1 To 6
Rows(i).Insert
Next
End Sub