Deleting empty rows with a twist

[austenr]

I want to utilize this code but in addition to deleting the empty rows I want to delete the row right before it. I have data that will always be two lines of data then a blank line. I have played around with this several ways and cant figure how to delete the row right before the blank one. Probably something simple that I am missing. Any suggestions?


Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

[CBrine]

Private Sub CommandButton1_Click()
Dim lastrow As String, r As Integer
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r - 1).Delete
Rows(r - 1).Delete
End If
Next r
End Sub



This should work. Did a quick test and everything was OK.

HTH
Cal

[austenr]

Hmmm....Doesn't seem to work for me attached is the workbook.

[Jacob Hilderbrand]

Something like this maybe. Also note that there was some data in your blank rows somewhere. Once I cleared the entire row it started working.


Option Explicit

Sub RemoveBadStuff()
Dim LastRow As String
Dim r As Integer
LastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountBlank(Range("A" & r).EntireRow) = 256 Then
Rows(r).Delete
Rows(r - 1).Delete
End If
Next r
End Sub

[austenr]

When I run your code I get Object variable or With Block variable not set

[Jacob Hilderbrand]

Try this:


Option Explicit

Sub RemoveBadStuff()
Dim LastRow As String
Dim r As Integer
LastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
For r = LastRow To 2 Step -1
If Application.WorksheetFunction.CountBlank(Range("A" & r).EntireRow) = 256 Then
Rows(r).Delete
Rows(r - 1).Delete
r = r - 1
End If
Next r
End Sub

[austenr]

Great job!! Thanks a lot