deleting blank rows

fastbuck

Board Regular
Joined
Apr 22, 2014
Messages
144
Hi have a large amount of information in column A. Every row of data is separated by 6 blank rows. Including the 6 blank rows it’s a total of 34897 rows!! I need to delete all the blank rows in-between therefore moving all the data up. I’m using the ‘go to special – blanks selection. And then once selected I use the ‘delete sheet rows’. This works okay if I choose a small number of rows at a time, although it is slow. However if I choose a large number of rows I get a ‘not responding’ message. I’ve removed all formatting and formulas thinking that may be affecting the process but this didn’t make any difference. Am I doing something wrong? Is there a way to get the job done efficiently? I have a number of sheets to do and this will take a lifetime the way I’m going :)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
If this is a one off...

Two ideas:

1) If the data order is not important you could select all rows, sort by a specific column.
This should place all the blank rows at the top of the spreadsheet. Then just delete them.
Re-sort if you want the data in a different order.

This may not work as you said you get a "not responding" message.
I get this too but after a delay of a minute or so Excel begins responding again.

2) You could save the file as CSV
Use Windows Sort
Import the CSV and delete the blank lines at the top of the file.
 
Upvote 0
How about something like:
Code:
Sub DeleteBlankRows()
    
    Dim MyRange As Range
    Dim MyTotal As Long
    Set MyRange = Range("A1:A34897")
    MyTotal = MyRange.Cells.Count
    
    For MyCount = MyTotal To 1 Step -1
        If Len(MyRange.Cells(MyCount).Formula) = 0 Then
            MyRange.Cells(MyCount).EntireRow.Delete
        End If
    Next
    
End Sub

Just change the range to match your requirements.

You have to delete rows from the bottom up, otherwise Excel gets a headache.

Cheers

Pete
 
Upvote 0
Have you tried switching Autofilter on, if it is not on already, selecting blanks in, say, column A, and selecting and deleting all the visible records?
 
Upvote 0
Another option is to click Find & Select > Go To Special > Blanks

It highlights the blanks and hit delete! Depends on your data whether this works or not!
 
Upvote 0
Hi all, thank you for the suggestions and the fast responses. as the order is important I used peterooneys macro and it works a treat...thanks pete. I love macros :) this will save me so much time. N
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top