Deleting Rows

[kkondrat1]

The code I am using is below-

I am trying to delete rows that contain an H in column C

here is what column "C" looks like-

H78
H78
H87
P38

The H will always appear on the left, so I am trying to use LEFT, no
sure how to do it? I tried below but it doesn't work.


Sub Delete_rows_based_on_Closing()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("C").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If UCase(Left(rng(i).Value)) = "H" _
And UCase(rng(i).Offset(0, 8).Value) = "CLOSING" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Su

 

[kkknie]

The structure is good, but there are a few problems with your code:

1. The rng variable will pick up only the text values, so .count ma
not reflect the entire range you want to work with.
2. The rng variable is not an array so you can't use rng(i)
3. Left works like this Left(CellValue, 1) to get the first characte
on the left.

I'd recode it like this:

Code
-------------------
Sub Delete_rows_based_on_Closing()

Dim i As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = Range("C65536").End(xlUp).Row To 1 Step -1
If UCase(Left(Range("C" & i).Value, 1)) = "H" And UCase(Range("K" & i).Value) = "CLOSING" Then
Range("C" & i).EntireRow.Delete
End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Su

 

[Bob Phillips]

Left needs a length

If UCase(Left(rng(i).Value,1)) = "H" _