Why does this macro skip the last row?

[Tigerexcel]

This macro is supposed to delete any blank rows, it does so except for the row above the last row.
The macro is:

Sub delrow()
Set rang = ActiveSheet.UsedRange
For i = rang.Rows.Count To 2 Step -1
If Cells(i, 2) = "" Then
Rows(i).Delete
End If
Next i
End Sub

A quick check through the Immediate window confirms that the all the rows are picked up in the UsedRange property, the rang.Rows.Count however returns row 17 rather than row 19. There is a difference in how the macro performs if the Jan title is located in cell B2 where it works fine, if the data is moved down a row so that the Jan title is in B3 then it doesn't pick up the last row.

Jan	Feb	Mar
100	200	300
110	220	330
120	240	360
130	260	390
140	280	420
150	300	450
160	320	480
170	340	510

 

[Michael M]

Maybe this way

Sub delrow()
Dim lr As Long, i As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
For i = lr To 2 Step -1
If Cells(i, 2) = "" Then
Rows(i).Delete
End If
Next i
End Sub

 

[Peter_SSs]

First: When posting code, please use code tags so that the code is formatted in an easier-to-read format.

If there is nothing in 1 or more rows at the top of the sheet, then UsedRange may not include those rows. In that case rang.Rows.Count is not the same as finding the last used row in the sheet & hence your code couls miss 1 (or more) rows.
Michael has suggested a safer way to find the last row.

Another way is to delete all the rows at once.
If the heading is in, say B3

Sub Del_Rows()
  On Error Resume Next
  Range("B3", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[Tigerexcel]

Hi Michael,

Yes, that works well. I wonder is it an inherent failing of the Rows.Count property or is there something else wrong with macro? It's a really simple macro so there can't be much that could have gone wrong.

 

[Michael M]

See @Peter_SSs post #3 regarding the use of UsedRange and / or rng.Rows.Count

 

[Tigerexcel]

Thanks Gentlemen for clearing that up. Sorry about the use of the code tags, I'll rectify that next time.
This one really had me puzzled because the correct UsedRange address was being returned so in theory the Rows.Count should have worked. Michael, I like the safer way of ensuring that it is picked up and Peter I love the economy of your coding, one line to do all that work and I thought mine was brief.

 

[Michael M]

Glad WE helped out....and yeah, @Peter_SSs is very adept at providing REALLY efficient code !!....?

 

[Peter_SSs]

the correct UsedRange address was being returned

Was being returned where as nothing in your posted code is returning an address?

What was the exact UsedRange address returned?

 

[Tigerexcel]

Was being returned where as nothing in your posted code is returning an address?

What was the exact UsedRange address returned?

$B$3:$D$19

 

[Peter_SSs]

$B$3:$D$19

Then as I explained before Rows.Count on this range gives 17, not 19. Therefore your loop checking each row will start at row 17 not row 19 & hence row 18 (or 19) never get checked.