Deleting Blank Rows

[brennaboy]

Hi,

I have about 3000 rows of excel data and some of these rows are blank, which I need to remove.

I could do a loop to check Cell A1 and if blank delete the entire row and then go onto the next row and so on, but I don't know how to end the loop as the cells at the end of the data will be blank.

How do you get it to end at the end of the data?

Also - is this the right way to go about it?

Cheers,

B.

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow 'Lastrow to 1 Step -1

If Cells(i, "A").Value2 = "" Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[gcomyn]

I'd use the 'Lastrow to 1 step -1'... since you are deleteting rows, that will not throw out the count.

GComyn

[Bob Phillips]

Oops, my bad. Forgot to delete the first part.

[Paul_Hossler]

If Cells(i, "A").Value2 = "" Then


It would seem that if there is data in (i,"B"), this would incorrectly delete the row

How about (not tested since I don't have Excel on this comp)


Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

For i = .UsedRange.rows.count to 1 Step -1

If application.worksheetfunction.counta(.rows(i)) = 0 then
.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub



Paul

[Bob Phillips]

How about (not tested since I don't have Excel on this comp)

What is the point of a computer without Excel on it?

[mikerickson]

What is the point of a computer without Excel on it?
^^Gaming. Paperweight. A way for the boss to receive e-mail.

For the OP question, if you are concerened about truly blank cells (as opposed to cells holding a formula that evaluates to "")

On Error Resume Next
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error Goto 0

[Aussiebear]

What is the point of a computer without Excel on it?

Does Bill know about this?

[Kenneth Hobs]

Similar to what Paul posted:
Sub DeleteBlankRows()
Dim Rw As Range
Dim lr As Long

Dim start As Double
start = Timer

If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 Then
MsgBox "No data found", vbOKOnly
Exit Sub
End If
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

For lr = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Range("A" & lr).EntireRow) = 0 Then
Range("A" & lr).EntireRow.Delete xlUp
End If
Next lr

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

MsgBox "Macro took " & (Timer - start) & " seconds to run."
End Sub

[badri.toppur]

This function ProcessData(), worked just fine on my dataset, to remove umpteen blank rows corresponding to page breaks in a pdf file.

Paul Hossler, Thank you once again.


It would seem that if there is data in (i,"B"), this would incorrectly delete the row

How about (not tested since I don't have Excel on this comp)


Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

For i = .UsedRange.rows.count to 1 Step -1

If application.worksheetfunction.counta(.rows(i)) = 0 then
.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub



Paul