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.
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I'd use the 'Lastrow to 1 step -1'... since you are deleteting rows, that will not throw out the count.
GComyn
Oops, my bad. Forgot to delete the first part.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
It would seem that if there is data in (i,"B"), this would incorrectly delete the rowIf Cells(i, "A").Value2 = "" Then
How about (not tested since I don't have Excel on this comp)
[VBA]
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
[/VBA]
Paul
What is the point of a computer without Excel on it?Originally Posted by Paul_Hossler
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
^^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 "")
[VBA]On Error Resume Next
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error Goto 0[/VBA]
Does Bill know about this?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Similar to what Paul posted:
[VBA]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[/VBA]
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.
Posting Permissions
