PDA

View Full Version : Deleting Blank Rows



brennaboy
08-12-2010, 08:52 AM
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
08-12-2010, 09:18 AM
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
08-12-2010, 03:30 PM
I'd use the 'Lastrow to 1 step -1'... since you are deleteting rows, that will not throw out the count.

GComyn

Bob Phillips
08-12-2010, 03:57 PM
Oops, my bad. Forgot to delete the first part.

Paul_Hossler
08-12-2010, 04:32 PM
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
08-12-2010, 04:35 PM
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
08-12-2010, 04:55 PM
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
08-12-2010, 06:55 PM
What is the point of a computer without Excel on it?

Does Bill know about this?

Kenneth Hobs
08-13-2010, 06:54 AM
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
12-22-2020, 01:32 AM
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