Removing excess rows

[jwpickett]

I've searched thru the board but can't find a solution to my problem.

I've got a spreadsheet that uses about 2200 rows and about 15 columns. However, when I try to insert a column, it freezes up the application. My assumption is that this is caused by the fact that there are in excess of 65,000 rows. When I try to highlight those 63,000 rows and delete them, the system freezes up as well.

These excess rows are unnecessary and I'm sure add to the file size.

Running Windows XP with Excel 2003.

Thanks

 

[texasalynn]

when you try to do things with that large data Excel memory generally can't process very well. I would suggest you try delete in smaller chunks. Or if you want a macro that would do a loop might work, but again that may take a while also. Post back with more information if you would like a macro to loop and remove the excess. Need to know what criteria you use to determine what to remove.

HTH

 

[jwpickett]

criteria is essentially blank rows.

Even in smaller chunks, i.e., 1000 rows at a time, it will take quite a long time to get rid of 60K rows.

 

[texasalynn]

not tested

make sure you make a copy of your file before try this

Sub excessrows()
Application.ScreenUpdating = False
Dim i As Long, LR As Long, Counter As Long
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = LR to 2 Step-1
    On Error Resume Next
    Rows(i).SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
    Counter = Counter + 1
    On Error GoTo 0
Next i

MsgBox "You have deleted " & Counter & " Rows"
Application.ScreenUpdating = True
End Sub

 

[jwpickett]

I ran that and it said that 63XXX rows were removed, but I can still see them. I saved the file hoping that would clear those unneeded rows, but to no avail.

When I "Go To" last active cell it goes to AM65344, when in actuality the last row is 2837.

 

[texasalynn]

after the macro runs, save the file
did you use the modified code? I edited it to run from the last row up to make it run properly, the other method was skipping rows (my bad)

 

[jwpickett]

Followed the instructions exactly and saved. Not getting rid of those annoying rows.

If you've got other things to do, don't worry about this.

 

[texasalynn]

I edited a post and this was changed

For i = LR to 2 Step-1

is that what you have?

 

[jwpickett]

Yes. Going to try to copy the info to a new tab and see if that resolves the problem. Theoretically, it should.

 

[texasalynn]

this needs to go into a module and not a tab. To do that press Alt + F11, insert module and paste the code I provided into the new module.

HTH