Cannot seem to delete unused rows from my worksheet

lordterrin

Board Regular
Joined
Mar 21, 2012
Messages
155
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.
Code:
    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.
Code:
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....
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

lordterrin

Board Regular
Joined
Mar 21, 2012
Messages
155
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:

Code:
 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
Code:
 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top