Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows help?
The following macro deletes all rows on the active worksheet
' that have 1034, 1035, 1037 column E. I am looking for someone to let me know how to do the opposite, delete all rows EXCEPT that that have 1034, 1035, 1037 column E. Thanks. Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then If del Is Nothing Then Set del = cell Else: Set del = Union(del, cell) End If End If Next On Error Resume Next del.EntireRow.Delete End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows help?
Jay,
Replace If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then with If (cell.Value) < "1034" _ And (cell.Value) < "1035" _ And (cell.Value) < "1037" Then HTH, Bernie MS Excel MVP wrote in message ups.com... The following macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. I am looking for someone to let me know how to do the opposite, delete all rows EXCEPT that that have 1034, 1035, 1037 column E. Thanks. Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then If del Is Nothing Then Set del = cell Else: Set del = Union(del, cell) End If End If Next On Error Resume Next del.EntireRow.Delete End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows help?
Using boolean logic this must be work...
If (cell.Value) < "1034" and (cell.Value) < "1035" and (cell.Value) < "1037" Then On Feb 27, 1:08 pm, wrote: The following macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. I am looking for someone to let me know how to do the opposite, delete all rows EXCEPT that that have 1034, 1035, 1037 column E. Thanks. Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then If del Is Nothing Then Set del = cell Else: Set del = Union(del, cell) End If End If Next On Error Resume Next del.EntireRow.Delete End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows help?
Maybe just reversing the logic:
Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then 'do nothing else If del Is Nothing Then Set del = cell Else Set del = Union(del, cell) End If End If Next cell On Error Resume Next del.EntireRow.Delete on error goto 0 End Sub I would think you'd want to check for numbers: If (cell.Value) = 1034 _ Or (cell.Value) = 1035 _ Or (cell.Value) = 1037 Then 'do nothing else .... And sometimes, it's easier to use "select case" instead of a bunch of or's: .... select case cell.value case is = 1034, 1035, 1037 'do nothing case else If del Is Nothing Then Set del = cell Else Set del = Union(del, cell) End If end select .... It can make it easier to read/understand. wrote: The following macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. I am looking for someone to let me know how to do the opposite, delete all rows EXCEPT that that have 1034, 1035, 1037 column E. Thanks. Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then If del Is Nothing Then Set del = cell Else: Set del = Union(del, cell) End If End If Next On Error Resume Next del.EntireRow.Delete End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows help?
On Feb 27, 2:14 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Jay, Replace If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then with If (cell.Value) < "1034" _ And (cell.Value) < "1035" _ And (cell.Value) < "1037" Then HTH, Bernie MS Excel MVP wrote in oglegroups.com... The following macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. I am looking for someone to let me know how to do the opposite, delete all rows EXCEPT that that have 1034, 1035, 1037 column E. Thanks. Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have 1034, 1035, 1037 column E. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "1034" _ Or (cell.Value) = "1035" _ Or (cell.Value) = "1037" Then If del Is Nothing Then Set del = cell Else: Set del = Union(del, cell) End If End If Next On Error Resume Next del.EntireRow.Delete End Sub thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |