Macro If And
 

Hi
anyone to help me on this ?

I have done this macro to delete the rows where in Colomn D = Heater.


Dim HTRCell As Range
Dim rngHTR As Range
Dim rngHTRCell As Range

Do
Set HTRCell = Cells(Rows.Count, "D").End(xlUp)
Set rngHTR = Range("D5", HTRCell)
Set rngHTRCell = rngHTR.Find("Heater")

If rngHTRCell Is Nothing Then
Exit Do
Else
Rows(rngHTRCell.Row).Delete
End If
Loop
End Sub

I would like to modify that condition as:
if D=Heater (as above) *and* B = blank value the delete row D

Regards
Herv

--
Herve_Ro
-----------------------------------------------------------------------
Herve_Rob's Profile: http://www.excelforum.com/member.php...fo&userid=3520
View this thread: http://www.excelforum.com/showthread.php?threadid=55006

Macro If And
 

Try the following macro. I modified yours to take out unnecessary steps and
used a For loop instead of a Do loop. Post back if you need more or if this
doesn't do what you want. Watch out for line wrapping in this message. HTH
Otto
Sub DeleteRows()
Dim rngHTR As Range
Dim i As Range
Set rngHTR = Range("D5", Range("D" & Rows.Count).End(xlUp))
For Each i In rngHTR
If i.Value = "Heater" And IsEmpty(Cells(i.Row, "B").Value) Then _
i.EntireRow.Delete
Next i
End Sub

"Herve_Rob" wrote
in message ...

Hi
anyone to help me on this ?

I have done this macro to delete the rows where in Colomn D = Heater.


Dim HTRCell As Range
Dim rngHTR As Range
Dim rngHTRCell As Range

Do
Set HTRCell = Cells(Rows.Count, "D").End(xlUp)
Set rngHTR = Range("D5", HTRCell)
Set rngHTRCell = rngHTR.Find("Heater")

If rngHTRCell Is Nothing Then
Exit Do
Else
Rows(rngHTRCell.Row).Delete
End If
Loop
End Sub

I would like to modify that condition as:
if D=Heater (as above) *and* B = blank value the delete row D

Regards
Herve


--
Herve_Rob
------------------------------------------------------------------------
Herve_Rob's Profile:
http://www.excelforum.com/member.php...o&userid=35204
View this thread: http://www.excelforum.com/showthread...hreadid=550064

Macro If And
 

Thanks Otto,
With yours, it seems only half of the rows are deleted (1 on 2).

If I have both conditions from rows 5 to 24, the 1st time i run th
macro, only 10 rows (5, 7, 9, 11, 13, 15, 17, 19, 21, 23) are delete
!?

If i run it again, 5 rows are deleted.....

Thank

--
Herve_Ro
-----------------------------------------------------------------------
Herve_Rob's Profile: http://www.excelforum.com/member.php...fo&userid=3520
View this thread: http://www.excelforum.com/showthread.php?threadid=55006

Macro If And
 

You're right. I used a For loop that went from top (D5) to bottom and I was
deleting rows as I went. That never works. When you're deleting rows you
have to go from bottom to top. The following corrected macro does that.
Sorry about that. Otto
Sub DeleteRows()
Dim rngHTR As Range
Dim c As Long
Set rngHTR = Range("D5", Range("D" & Rows.Count).End(xlUp))
For c = rngHTR.Count To 1 Step -1
If rngHTR(c) = "Heater" And IsEmpty(rngHTR(c).Offset(, -2).Value)
Then _
rngHTR(c).EntireRow.Delete
Next c
End Sub
"Herve_Rob" wrote
in message ...

Thanks Otto,
With yours, it seems only half of the rows are deleted (1 on 2).

If I have both conditions from rows 5 to 24, the 1st time i run the
macro, only 10 rows (5, 7, 9, 11, 13, 15, 17, 19, 21, 23) are deleted
!?

If i run it again, 5 rows are deleted.....

Thanks


--
Herve_Rob
------------------------------------------------------------------------
Herve_Rob's Profile:
http://www.excelforum.com/member.php...o&userid=35204
View this thread: http://www.excelforum.com/showthread...hreadid=550064

Macro If And
 

it works fine
Cheers


--
Herve_Rob
------------------------------------------------------------------------
Herve_Rob's Profile: http://www.excelforum.com/member.php...o&userid=35204
View this thread: http://www.excelforum.com/showthread...hreadid=550064