VBA : Delete Blank rows

[ssingh75]

Dear All,

Can anyone please let me know the Macro to delete blank rows from entire sheet...

Thanks in advance..

Susheel Singh

 

[Trevor G]

look to use something like this as I am selecting the entire used range and then deleting empty rows. Test on another sample workbook.

Sub usedR()
ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[ssingh75]

Thanks for your reply...

but looking to delete blank cells of entire columns in Excel...

Susheel Singh

 

[Firefly2012]

Perhaps:

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Amend column as appropriate

 

[Trevor G]

Thanks for your reply...

but looking to delete blank cells of entire columns in Excel...

Susheel Singh

Your thread states Rows....

 

[ravi4ever]

Perhaps..

Sub DeleteAllBlankCells()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    Range("A1").Select
End Sub

 

[alexobrien90]

Perhaps:

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Amend column as appropriate

This one worked great for me!

 

[thamestowers]

Perhaps:

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Amend column as appropriate

This one worked perfectly for me!! Thank you Firefly2012!!! >D

 

[jcgilbertson]

Hi, I am using the first solution mentioned to delete rows. However, when it gets to a line containing pivot table data the CountA function is not returning a value greater than 0. I then get an error saying it is unable to delete rows containing a pivot table, and my macro ultimately fails. Any ideas for a solution that will account for rows containing pivot table data? I want these rows to be considered "used" or "not blank" so that they are not deleted.

 

[hdshank]

I downloaded a number of VBA code sets to delete blank rows and Firefly's is the only one that worked.