Excel sheet has 30742 rows, but only 105 have data - how to clean up?

[Ploughguy]

Hi,

I have a spreadsheet that has 105 rows of data.  I wrote a macro to insert some blank rows between each data row and it took an entire dog-walk to run.  The macro finds the last populated row and inserts a new row above, then steps its way up.

This macro tells me the sheet has 30742 rows.  30637 of them contain nothing interesting.  Clearly I have inadvertently added them by doing something weird. Probably too much agile in my VBA and not enough thinkage.

I have tried selecting and deleting all rows below the last one I care about (Ctrl-shift-down then Delete) - that didn't work.  Just for good measure, I selected as above and set the cell formatting to normal and cleared contents - still has 30k rows.

Is there any way to get Excel to discard the unpopulated rows in the sheet? 

Thanks for your help.

Sub RowsAndCols()
  Dim lastRow As Long
  Dim lastCol As Long
'Find last row
    lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    MsgBox "Insert Blanks - Last row is " & lastRow
' Find Last Column
    lastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).column
    MsgBox "Insert Blanks - Last column is " & lastCol
End Sub

[#2 · Accepted solution]

Deleting the rows and clearing the cells will not update the last row unless you save your file after deleting rows and clearing cells.

[#3]

Ick. But OK, I can see why that might be the case.
I just tried it and it did exactly as you said. I have only been at this VBA caper for a week so some of the - how to put this delicately - ah... more arcane eccentricities of Excel are still becoming apparent.
However, it ~is~ bloody marvellous what Excel and VBA can do though. I'm having a rollicking time mucking about with it. I think Excel is possibly the most remarkable piece of software I have ever played with.

Thanks for your help. Your answer is perfect!

[#4]

You're welcome @Ploughguy! Glad I could help.

Btw this is default behavior of Excel for last cell and it has got nothing to do with VBA.

And I agree with whatever you said about Excel and VBA. :)

Have fun with Excel & VBA!