Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell values.
I am trying to expand on my simple macro to:
Look in column B and if it finds the value "No Employee" to select that row and delete it. I also would like look in column C for values that are 500 or less and delete that row. My data could be as little as 75 rows to several hundred rows. And these values could appear several times in there respective columns. I'm not sure how to get the code to delete multiple instances. Sub RemoveEmployee() If Range("B5").Value = "No Employee" Then Rows("5:5").Select Selection.Delete Shift:=xlUp End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell values.
One way is one at a time such as
for i=cells(rows.count,"b").end(xlup).row to 2 step -1 if cells(i,"b")="No Employee" or cells(i,"c")<500 then rows(i).delete next -- Don Guillett Microsoft MVP Excel SalesAid Software "Little Penny" wrote in message ups.com... I am trying to expand on my simple macro to: Look in column B and if it finds the value "No Employee" to select that row and delete it. I also would like look in column C for values that are 500 or less and delete that row. My data could be as little as 75 rows to several hundred rows. And these values could appear several times in there respective columns. I'm not sure how to get the code to delete multiple instances. Sub RemoveEmployee() If Range("B5").Value = "No Employee" Then Rows("5:5").Select Selection.Delete Shift:=xlUp End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell values.
This will start at the last row containing data in column B and run
backwards to row #2. At anytime during the cycle, if column B of the row = "No Employee" or column C of the row is less than or equal to 500, the row will be deleted. Sub RemoveEmployee() Dim r As Long For r = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Cells(r, 2).Text = "No Employee" Or _ Cells(r, 3).Value <= 500 Then Rows(r).EntireRow.Delete Next r End Sub Little Penny wrote: I am trying to expand on my simple macro to: Look in column B and if it finds the value "No Employee" to select that row and delete it. I also would like look in column C for values that are 500 or less and delete that row. My data could be as little as 75 rows to several hundred rows. And these values could appear several times in there respective columns. I'm not sure how to get the code to delete multiple instances. Sub RemoveEmployee() If Range("B5").Value = "No Employee" Then Rows("5:5").Select Selection.Delete Shift:=xlUp End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I conditionally delete rows based on cell contents? | Excel Worksheet Functions | |||
Deleting rows based on values in a a cell in the row | New Users to Excel | |||
Delete duplicate rows based on part of cell. | Excel Programming | |||
Delete rows based on Cell name | Excel Programming | |||
Hide/Delete entire rows based in the content of one cell | Excel Discussion (Misc queries) |