large size spreadsheet - delete the blank stuff

[BrutalLogiC]

hey I'm struggling to use a spreadsheet which is 75mb so trying to cut down the amount of blank/empty rows columns

on a few of the sheets there's 50k rows with data but then on about a dozen other sheets there's "only" 10k rows with data and 40k empty rows, the scroll bars are very small

is there an easy way to delete? I've not come up with any method that works well

when I try copy the sheet into a new workbook it seems to copy all the blank as well so maybe I can select all the cells with data easily to do this rather than scrolling through 50k of lines?

tyvm

 

[BrutalLogiC]

I've started just copying the individual sheets into a new workbook, slow but painful, seems to be working

 

[vcoolio]

Hello BrutalLogic,

Following is something I did for another Poster on another forum a couple of years ago. It was for a large data set over four sheets with many blank rows also.

Sub DeleteStuff()

    Dim ws As Worksheet
    
Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Range("A2", ws.Range("H" & ws.Rows.Count).End(xlUp)).Sort ws.[A2], 1

With ws.Range("A1", ws.Range("A" & ws.Rows.Count).End(xlUp))
        .AutoFilter 1, ""
        .Offset(1).EntireRow.Delete
        .AutoFilter
        End With
Next ws

Application.ScreenUpdating = True

End Sub

The key to helping it speed up was to sort all the data by Column A which ended up putting all the blank rows at the bottom of each data set in each sheet.
The data set in each sheet spanned Columns A:H and some 100K rows.

I'm not sure how effective it could be for your situation but there's no harm in trying!

Following is a link to the sample the Poster supplied (all with dummy data). Click on the "RUN" button to see it work.

http://ge.tt/1AGAbdp2

Test it in a copy of your work book also.

I hope that this helps.

Cheerio,
vcoolio.

 

[Fluff]

Another option, if your blanks are below the data

Sub delExcess()
   Dim Ws As Worksheet
   For Each Ws In Worksheets
      Ws.Rows(Ws.Cells.Find("*", , xlValues, , , xlPrevious, , , False).Row & ":1048576").Delete
   Next Ws
End Sub