Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |