large size spreadsheet - delete the blank stuff

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
I've started just copying the individual sheets into a new workbook, slow but painful, seems to be working
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
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.


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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,548
Office Version
  1. 365
Platform
  1. Windows
Another option, if your blanks are below the data
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,398
Messages
5,528,503
Members
409,820
Latest member
gabrielrms

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top