Tigerexcel
Active Member
- Joined
- Mar 6, 2020
- Messages
- 493
- Office Version
- 365
- 2019
- Platform
- Windows
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.
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 |