Macro to Delete Rows Where there is a value in Col H from row 2 onwards

[howard]

I have tried to write code to delete all rows where there is a value in Col H on sheet2 from row 2 onwards

I get a run time error "Invalid pprocedure call or argument" and the code below is highlighted

If .Cells("H" & I).Value <> "" Then

 Sub Delete_row_ColH()
Dim LR As Long, I As Long
With Sheets(2)
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
For I = LR To 2 Step -1
  If .Cells("H" & I).Value <> "" Then
  .Cells("H" & I).EntireRow.Delete
End If
Next I
End With
End Sub

It would be appreciated if someone could kindly amend my code

 

[Micron]

I don't think you can use column letters with Cells property. Try
If Range("H" & I).Value <> "" Then
Also substitute Cells with Range in the delete line: Range("H" & I).EntireRow.Delete

Shouldn't you be counting the rows in column H and not A?

 

[howard]

Thanks for the help Micron. Your suggestion worked 100%

 

[HaHoBe]

Hi Micron,

I don't think you can use column letters with Cells property.

Cells expects the rownumber first and will accept both Numbers and Letters for Columns as second argument. So correct call would be

  If .Cells(I, "H").Value <> "" Then

Holger

 

[Micron]

Cells expects the rownumber first

There should be a forehead slapping emoji.

 

[mumps]

Without looping:

Sub DeleteRows()
    On Error Resume Next
    Columns("H").Replace "*", "#N/A", xlWhole, , False
    Columns("H").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End Sub

 

[Micron]

Can you explain how that avoids row 1 as requested? I'm not seeing it. Or would it not be possible using that method?
Thanks.

 

[mumps]

Oops! Missed that point. Thanks,
Revised code:

Sub DeleteRows()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error Resume Next
    Range("H2:H" & lRow).Replace "*", "#N/A", xlWhole, , False
    Columns("H").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End Sub

 

[howard]

Thanks for the help, Holger and Mumps

 

[mumps]

Glad to help.