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

--
kkondrat
-----------------------------------------------------------------------
kkondrat1's Profile: http://www.excelforum.com/member.php...nfo&userid=600
View this thread: http://www.excelforum.com/showthread.php?threadid=26547

Left needs a length

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

--

HTH

RP

"kkondrat1" wrote in message
...

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, not
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 Sub


--
kkondrat1
------------------------------------------------------------------------
kkondrat1's Profile:
http://www.excelforum.com/member.php...fo&userid=6002
View this thread: http://www.excelforum.com/showthread...hreadid=265475

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
-------------------

Basically, loop from the last used cell in column C up to C1. Chec
the C column for a value starting with H and the K column for CLOSING.
If found, delete it.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26547