Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row based on more than one condtion
I am using the following code to delete a row in a spreadsheet based
on the value (zero) in that row: Sub RemoveZero() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If RngColAB(c).Value = 0 Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub How can I modify it so that it looks to two columns where both rows have to have a zero value so that the row can be deleted? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row based on more than one condtion
change the line:
If RngColAB(c).Value = 0 Then _ to If RngColAB(c).Value = 0 And Application.Trim(RngColAB(c).Offset(0, 7).value) = 0 Then _ where the 7 is a value I've chosn at random representing 7 cells to the right of your cell being tested for 0. A 1 would be one cell to the right, a -1 would be one cell to the left etc. etc. (not tested). -- p45cal "Opal" wrote: I am using the following code to delete a row in a spreadsheet based on the value (zero) in that row: Sub RemoveZero() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If RngColAB(c).Value = 0 Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub How can I modify it so that it looks to two columns where both rows have to have a zero value so that the row can be deleted? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row based on more than one condtion
Try this:
RngColAB(c).offset(0,1) 'One cell to the right RngColAB(c).offset(1,0) 'One cell below HTH, Barb Reinhardt If RngColAB(c).Value = 0 Then _ "Opal" wrote: I am using the following code to delete a row in a spreadsheet based on the value (zero) in that row: Sub RemoveZero() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If RngColAB(c).Value = 0 Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub How can I modify it so that it looks to two columns where both rows have to have a zero value so that the row can be deleted? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row based on more than one condtion
On Aug 25, 7:38 pm, Barb Reinhardt
wrote: Try this: RngColAB(c).offset(0,1) 'One cell to the right RngColAB(c).offset(1,0) 'One cell below HTH, Barb Reinhardt If RngColAB(c).Value = 0 Then _ "Opal" wrote: I am using the following code to delete a row in a spreadsheet based on the value (zero) in that row: Sub RemoveZero() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If RngColAB(c).Value = 0 Then _ RngColAB(c).EntireRow.Delete Thank you, both. That worked really well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
condtion for entering date | Setting up and Configuration of Excel | |||
delete row based on value of ... | New Users to Excel | |||
How to reset Excel 2002 to first run condtion | Excel Discussion (Misc queries) | |||
sumif with 2 condtion | Excel Worksheet Functions | |||
delete based on | Excel Discussion (Misc queries) |