Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Macro based on condition in Two Columns
Hello,
I have these two macros. there similar one delete rows based on value in Col. C and the other deletes rows based on values in Col A. Now I want to combine the two so I only have one macro to run. here are both Macros Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub And the second one is Sub DeleteUnusedRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column C LastRow = Range("C" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= 0, delete row If Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Macro based on condition in Two Columns
Hadi,
This now combines the 2 but note I made a change to column C. In your code If Cells(i, "C") = 0 Then Rows(i).Delete would delete the row if the cell was empty OR zero. In my code it must contain a 0 to be deleted Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Or Cells(i, "C") < "" _ And Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub Mike "Hadi" wrote: Hello, I have these two macros. there similar one delete rows based on value in Col. C and the other deletes rows based on values in Col A. Now I want to combine the two so I only have one macro to run. here are both Macros Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub And the second one is Sub DeleteUnusedRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column C LastRow = Range("C" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= 0, delete row If Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Macro based on condition in Two Columns
Great! it works.
thanks Mike. "Mike H" wrote: Hadi, This now combines the 2 but note I made a change to column C. In your code If Cells(i, "C") = 0 Then Rows(i).Delete would delete the row if the cell was empty OR zero. In my code it must contain a 0 to be deleted Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Or Cells(i, "C") < "" _ And Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub Mike "Hadi" wrote: Hello, I have these two macros. there similar one delete rows based on value in Col. C and the other deletes rows based on values in Col A. Now I want to combine the two so I only have one macro to run. here are both Macros Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub And the second one is Sub DeleteUnusedRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column C LastRow = Range("C" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= 0, delete row If Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Macro based on condition in Two Columns
Glad to help and thanks for the feedback
"Hadi" wrote: Great! it works. thanks Mike. "Mike H" wrote: Hadi, This now combines the 2 but note I made a change to column C. In your code If Cells(i, "C") = 0 Then Rows(i).Delete would delete the row if the cell was empty OR zero. In my code it must contain a 0 to be deleted Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Or Cells(i, "C") < "" _ And Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub Mike "Hadi" wrote: Hello, I have these two macros. there similar one delete rows based on value in Col. C and the other deletes rows based on values in Col A. Now I want to combine the two so I only have one macro to run. here are both Macros Sub DeleteBlankPCCRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column A LastRow = Range("A" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= blank, delete row If Cells(i, "A") = "" Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub And the second one is Sub DeleteUnusedRows() Dim i As Long, LastRow As Long Application.ScreenUpdating = False 'finds last used row in column C LastRow = Range("C" & Rows.Count).End(xlUp).Row 'begin loop to check each row For i = LastRow To 7 Step -1 'if cell= 0, delete row If Cells(i, "C") = 0 Then Rows(i).Delete Next i Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Macro - Delete Row Based on Condition-Modified Ron DeBruin Ma | Excel Programming | |||
how to delete a row based on a condition | Excel Programming | |||
Delete rows based on more than one condition. | Excel Programming | |||
Macro to delete rows based on a condition | Excel Worksheet Functions | |||
Delete Columns based on a condition | Excel Programming |