Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to I Delete a Row if a Cell does not have a particular value?
My Goal:
Run a Macro that will scan a worksheet and if the word "Accounting" is not in the "M" column of a Row then delete that Row. Here is what I have now. It scans the Worksheet but does not delete the rows: Sub Make_one_sheet_per_dept() ' ' Make_one_sheet_per_dept Macro ' Macro recorded 6/2/2008 Totals each department onto it's own worksheet. ' ' Sheets("Accounting").Select Dim rngTotal As String For i = 5000 To 1 Step -1 rngTotal = Range("M" & i).Select rngTotal = Range("M" & i).Value 'rngTotal = Trim(rngTotal) If rngTotal = "<Accounting" Then Rows(ActiveCell.Row).Select Selection.Delete shift:=x1Up End If Next i End Sub Thanks Will |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to I Delete a Row if a Cell does not have a particular value?
Sub Make_one_sheet_per_dept()
Dim rngTotal As String With Sheets("Accounting") For i = 5000 To 1 Step -1 If .Range("M" & i).Value < "Accounting" Then .Rows(i).Delete End If Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Will" wrote in message ... My Goal: Run a Macro that will scan a worksheet and if the word "Accounting" is not in the "M" column of a Row then delete that Row. Here is what I have now. It scans the Worksheet but does not delete the rows: Sub Make_one_sheet_per_dept() ' ' Make_one_sheet_per_dept Macro ' Macro recorded 6/2/2008 Totals each department onto it's own worksheet. ' ' Sheets("Accounting").Select Dim rngTotal As String For i = 5000 To 1 Step -1 rngTotal = Range("M" & i).Select rngTotal = Range("M" & i).Value 'rngTotal = Trim(rngTotal) If rngTotal = "<Accounting" Then Rows(ActiveCell.Row).Select Selection.Delete shift:=x1Up End If Next i End Sub Thanks Will |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird: inside of a cell I have a small box or cell I can't delete. | Excel Discussion (Misc queries) | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
How to delete cell values withour deleting cell formulae | Excel Discussion (Misc queries) | |||
How to delete cell values without affecting cell formulae | Excel Discussion (Misc queries) | |||
Delete cell contents with input to adjacent cell | Excel Discussion (Misc queries) |