Cannot seem to delete unused rows from my worksheet

[lordterrin]

I have a workbook that has about 500 rows of actual data in it that I get from an external source.

However, when I use the scroll bar, the rows go down to 1046977, and it makes it really difficult for me to find the row I want. It also makes copying&pasting using macros take a LONG time since it's copying all the way down to row 1046977.

I've tried four different macros to clear this out, but none of them work....

Here is what I've done so far:

1. Save the file.

2.

    Range("B6").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

This does nothing.  It clears the rows that don't contain data, but... the "rows" still exist in my sheet, (and on the scroll bar...)

3.

With Sheet1.Range("B:B")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

(This gives me the error "Unable to get the SpecialCells property of the Range class)

I'm not sure what else to try.... I will note that while Column A does exist, and A5 has data in it, at this stage in the macro the rest of column A is completely blank. Not sure if that has anything to do with this....

 

[lordterrin]

I figured it out by ending the macro in different spots and using the following code to determine WHERE this massive amount of cells was being added:

 MsgBox ActiveSheet.UsedRange.Rows.Count

I found that when I was copying data from my main sheet into a secondary sheet I had created, copying from (A1:Y " & ActiveSheet.UsedRange.Rows.Count) copied from row 1 down to row 1046977 since cells A2:A1046977 were empty.

To fix this, I changed the macro to copy only from (B1:Y " & ActiveSheet.UsedRange.Rows.Count), since column B had data down to row 2000 or so, which is where the actual sheet truly ended, then doing a force save

 ThisWorkbook.Saved = True

once the macro was done to eliminate any unused rows.

In case anyone else runs into this problem, maybe my solution will help you.