DELETE BLANK CELLS

[tran780]

Hi all,

I'd like to delete rows that are blank, my code below
I have to run the code 4-5 times to get result. Can anyone help to explain for me ?

Sub test()
Dim lastrow2, x As Integer
lastrow2 = Sheets("GPE").Range("A2").CurrentRegion.Rows.Count
For x = 2 To lastrow2
If Sheets("GPE").Cells(x, 4) = "" Then
Sheets("GPE").Rows(x).EntireRow.Delete
End if
Next x
End Sub

 

[Michael M]

When deleting rows you MUST always start at the bottom and work up....your current code is skipping rows, because when a row is deleted everything moves up and a "new" blank row is missed.
Maybe try it this way...IF you cells are truly blank, not blank because of a formula result

Sub MM1()
  On Error Resume Next
  Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[Peter_SSs]

When deleting rows you MUST always start at the bottom and work up....your current code is skipping rows, because when a row is deleted everything moves up and a "new" blank row is missed.
Maybe try it this way...IF you cells are truly blank, not blank because of a formula result

Sub MM1()
  On Error Resume Next
  Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

That does not specifically refer to the relevant sheet but perhaps more importantly, if there are blanks at the bottom of column D within the relevant worksheet range that code will not delete those rows.
A modified version that addresses those issues is:

Sub DelRws_1() 
  With Sheets("GPE")
    On Error Resume Next
    .Range("D2:D" & .Range("A2").CurrentRegion.Rows.Count).SpecialCells(xlBlanks).EntireRow.Delete
  End With
End Sub

@tran780
I am wondering how big (number of rows) your "GPE" sheet is? If large, the above code may be relatively slow and a faster deleting method could be provided.

 

[Marc L]

Can anyone help to explain for me ?

Hi,​

​

just apply a sort, faster than deleting anything in case of big data …​