Eliminating blank rows in data

[HC7531]

Good Afternoon Again:

I cannot sort my spreadsheet as all of the fields are not populated yet with data.

However, the spreadsheet is 292 pages in length due to the way the data migrated from our database. e.g for every row of data there is a blank row of data separating it from the next row of data.

Is there a quick way in excel to eliminate blank rows of data wihtout manipulating the spreadsheet?

Thank you.

CH

 

[rpaulson]

try this, adjust rang as needed.

Sub DeleteRows()
'This macro will delete the entire row if there is a data match anywhere in the range.
    Application.ScreenUpdating = False

    With Range("A1:A200")
        .Replace "", True, xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With

    Application.ScreenUpdating = True

End Sub

Ross

 

[Jonmo1]

You can highlight a column containing blanks
Press CTRL + G
Special
Blanks
OK
Right click any highlighted cell - Delete
EntireRow
OK.


Hope that helps.

 

[HC7531]

Thank you.

I will try both approaches.

CH

 

[brettdj]

The approaches above will delete rows that contain at least one nlank cell in the usedrange. Which are quick methods if that is what you are chasing

If you did want to contain the delete process to blank rows only then something like this

hth

Dave

'From the Menu, choose Insert-Module.
'Paste the code into the right-hand code window.
'Press Alt + F11 to close the VBE
'Go to Tools & Macro & Macros and double-click DeleteBlankRows

Sub DeleteBlankRows()
    Dim r As Long
    Dim AppCalc As Long

    With Application
        AppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        If Application.CountA(ActiveSheet.UsedRange.Rows(r)) = 0 Then ActiveSheet.UsedRange.Rows(r).Delete
    Next

With Application
    .ScreenUpdating = True
    .Calculation = AppCalc
End With
End Sub

 

[HC7531]

A special thanks to jonmo1.

I have limited experience developing macros, but I was able to reduce my lines of data by 3000 from over 8000 which helped enormously.