Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to delete a row based on criteria
I have a document with 6 columns of data and an unspecified number of rows.
I would like a macro to delete any row that has both columns 5 and 6 equal to 0. I would then need every other row to move up or down so there are no blank rows. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to delete a row based on criteria
Untested but should work.
Sub delRws() Dim lr As Long, i As Long lr = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row For i = lr To 2 Step -1 With ActiveSheet If .Cells(i, 5) = 0 And .Cells(i, 6) = 0 Then .Cells(i, 1).EntireRow.Delete End If End With Next End Sub "aileen" wrote: I have a document with 6 columns of data and an unspecified number of rows. I would like a macro to delete any row that has both columns 5 and 6 equal to 0. I would then need every other row to move up or down so there are no blank rows. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to delete a row based on criteria
Public Sub ProcessData()
Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 1 Step -1 If .Cells(i, "E").Value = 0 And .Cells(i, "F").Value = 0 Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "aileen" wrote in message ... I have a document with 6 columns of data and an unspecified number of rows. I would like a macro to delete any row that has both columns 5 and 6 equal to 0. I would then need every other row to move up or down so there are no blank rows. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to delete a row based on criteria
Both of those worked. Thanks so much.
"Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 1 Step -1 If .Cells(i, "E").Value = 0 And .Cells(i, "F").Value = 0 Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "aileen" wrote in message ... I have a document with 6 columns of data and an unspecified number of rows. I would like a macro to delete any row that has both columns 5 and 6 equal to 0. I would then need every other row to move up or down so there are no blank rows. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete part of a row based on certain criteria | Excel Worksheet Functions | |||
Macro to delete row based on criteria (first few characters of string) | Excel Programming | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Cut and Paste macro based on criteria then delete empty rows | Excel Programming |