Delete Empty Rows is range

[buzz71023]

I am trying to delete all the empty rows in a range. What I currently have deletes the rows but skips over a lot as the code runs. Below is what I currently have.

'msgbox delete blanks???
If MsgBox("Are you sure you want to delete ALL the blank rows in the chart?", vbYesNo, "Delete Blanks?") = vbNo Then
Exit Sub
Else


''''***PCrng is named range = A2:A1000***
For Each cell In Range("PCrng")
    If cell.Value = "" Then
             Set rng2 = cell.Offset(0, 3)
             Range(cell.Offset(0, -1), rng2).Select
             Selection.EntireRow.Delete
           Else
        End If
   Next cell
  Range("a1").Select
 MsgBox "There are no more results for " & myItem
 Exit Sub
End If

End Sub

 

[Jonmo1]

If the cells are truely blank (not formulas returing ""), then try

Sub DeleteBlanks()
On Error Resume Next
Range("PCrng").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[Jonmo1]

If the cells are NOT truely blank, and contain formulas returing "", then you have to loop backwards (from the last row to the first)

Try

Sub DeleteBlanks()
With Range("PCrng")
    For i = .Rows.Count To 1 Step -1
        If .Cells(i, 1) = "" Then .Cells(i, 1).EntireRow.Delete
    Next i
End With
End Sub

 

[buzz71023]

I am trying to delete all the empty rows in a range. What I currently have deletes the rows but skips over a lot as the code runs. Below is what I currently have.

'msgbox delete blanks???
If MsgBox("Are you sure you want to delete ALL the blank rows in the chart?", vbYesNo, "Delete Blanks?") = vbNo Then
Exit Sub
Else


''''***PCrng is named range = A2:A1000***
For Each cell In Range("PCrng")
    If cell.Value = "" Then
             Set rng2 = cell.Offset(0, 3)
             Range(cell.Offset(0, -1), rng2).Select
             Selection.EntireRow.Delete
           Else
        End If
   Next cell
  Range("a1").Select
 MsgBox "There are no more results for " & myItem
 Exit Sub
End If

End Sub

This was perfect. Much more efficient as well. Thanks for the help!

 

[Jonmo1]

Glad to help, thanks for the feedback.