Need help please with code to delete rows where a specified cell is blank

Hi guys

I hope someone can help me with this.

I recently received help from anther forum member to complete a project I was working on, and got stuck.

Basically... there is various info in a worksheet, that is copied to another worksheet. Since the data that is being copied is filled with formulas, when it is pasted, only the values are pasted.

Now I have 2 columns that needs to be checked for "blanks" but for some odd reason, the blanks in column "H", even though it shows nothing, excel does not pick it up as a blank.

The columns I want to be checked for blanks are columns A and H.

I have searched the forum, and read multiple thread, trying to find a solution, but there is only 1 formula I could find that works for column H, but it takes forever to complete.

There is approximately 14000 lines that gets copied, but I need to delete the blanks in column H.

The 2 codes I used is as follows:

Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.EntireRow.Delete

 Dim i
    Dim lr As Long

    With ThisWorkbook.Sheets("VIP") '**** AMEND THE SHEET NAME TO SUIT ****
        lr = .Cells(Rows.Count, "H").End(xlUp).Row
        For i = lr To 2 Step -1
            If IsError(.Cells(i, "H")) Then
                .Cells(i, "H").EntireRow.Delete
            ElseIf Cells(i, "H") = "" _
            Or .Cells(i, "H") = 0 Then
                .Cells(i, "H").EntireRow.Delete
            End If
        Next i
    End With

Like i said, the formula for column H works, but it takes a very long time to complete. Is there any way to make it run smoother/quicker?

The sheet that needs to be evaluated is attached as sample.xlsx

thank you for any input.

Regards

How about

Sub DelRows()
With Sheets("VIP")
   .UsedRange.Value = .UsedRange.Value
   .Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
   .Range("H:H").SpecialCells(xlBlanks).EntireRow.Delete
End With
End Sub

WOW... thank you

That did it.

Wish I knew why it didn't pick up my cells as blanks though.

Thank you very much for your help sir.

Glad to help & thanks for the feedback.

Where you have a formula that returns "" and you copy/paste as value you will end up with a Null String.
If you put these 2 formulae in a blank cell
=ISBLANK(H5)
=LEN(H5)

the first returns FALSE & the 2nd returns 0
Which shows that the cell is not truly blank, however that can be dealt with by using

.UsedRange.Value = .UsedRange.Value

This effectively "removes" the Null String

oh.... I see.

Thank you once again sir. I really appreciate it.