Remove Blank Rows in Excel / CSV file

Hi,

I have a few lists that I need to standardise. They are CSV files and have blank rows in them.

How can I remove any blank rows programmatically in Excel?

Thanks,

Ben
intangiblemediaAsked:
Who is Participating?
 
gtglonerConnect With a Mentor Commented:
...hmmm, forgot one crucial line of code, use this instead:
Sub Macro1()

    Columns("A:B").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Range("A1:B65536").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

Open in new window

0
 
gtglonerCommented:
Here is some example code of how it might be done. This assumes that your data is in columns A and B:
Sub Macro1()

    Columns("A:B").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Range("A1:B65536").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

Open in new window

0
 
MWGainesJRConnect With a Mentor Commented:
if your csv has 8000 rows or less, this will do it much more efficiently:

Sub delete_blank_rows()
ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Delete
End Sub

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
MWGainesJRCommented:
sorry, my macro is for deleting cells not rows....sorry......disregard it
0
 
nutschConnect With a Mentor Commented:
if all blank rows have the first cell empty, and all non-blank rows have their first cell full, you can use MWGainesJR's solution with a tweak.

THomas

Sub delete_blank_rows()
ActiveSheet.columns("A").SpecialCells(xlCellTypeBlanks).entirerow.Delete
End Sub

Open in new window

0
 
nutschCommented:
Warning on gtgloner's solution. Just as mine, it assumes that if the cell in column A is empty, then the whole row is empty.

Let us know if that's not the case and we can adjust the code to fit your needs,

Thomas
0
 
DaveConnect With a Mentor Commented:
The usual way to delete rows if the entire row is blank is to loop bottom up checking for empty cells using COUNTA

The SpecialCells approach is the way to go if the shortcut is appropriate

Cheers

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 rng1 As Range
    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

Open in new window

0
 
intangiblemediaAuthor Commented:
thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.