Delete blank rows

[tlindeman]

I have a column A, on sheet called SHEET2 Could you please provide me with simple VBA that deletes all rows where there is no data in columnA. My rows are from A1:A2941.

THank You
Tony

 

[JackDanIce]

Something I use:

Sub DeleteVisibleRows()
 
Dim i As Long, j As Long
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
With Sheets("Sheet2")
    If .AutoFilterMode Then .AutoFilterMode = False
    i = Application.Max(2941, .Range("A" & Rows.Count).End(xlUp).row)
    j = .Cells(1, Columns.Count).End(xlToLeft).Column
    With .Range("A1", Cells(i, j))
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="="
    End With
    .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1).Rows.Delete
    .AutoFilterMode = False
End With
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

 

[pboltonchina]

Try this on a copy of your data

Sub Delete_Blanks()
Application.ScreenUpdating = False
      Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value = "" Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

 

[Jonmo1]

Try

Sheets("Sheet2").Range("A1:A2941").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Hope that helps.

 

[JackDanIce]

Ughhh - talk about a shorter, quicker bit of code! I'm going to modify and steal that, thanks jonmo!

 

[Jonmo1]

Ughhh - talk about a shorter, quicker bit of code! I'm going to modify and steal that, thanks jonmo!

Keep in mind that specialcells(xlcelltypeblanks) will ignore formula blanks (="")
Even after that formula is copy / pastespecial / values
It is still not considered blank.


In that case, you would do

Range(....).Value = Range(....).Value

That converts them to true blanks.

THEN do the specialcells(xlcelltypeblanks).entirerow.delete

 

[JackDanIce]

As a modification, for something I'm thinking of for what I use it for, I could just filter and then delete visible rows.

Thanks for the heads up though

 

[Rick Rothstein]

Keep in mind that specialcells(xlcelltypeblanks) will ignore formula blanks (="")
Even after that formula is copy / pastespecial / values
It is still not considered blank.

In that case, you would do

Range(....).Value = Range(....).Value

That converts them to true blanks.

THEN do the specialcells(xlcelltypeblanks).entirerow.delete

As long as you are going to do the Range(..).Value = Range(..).Value operation in code, there is no need to perform the Copy/PasteSpecial/Values operation at all since the VB statement will handle moving the values into the cells as well as convert formula blanks to real blanks.