Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to delete rows that contain text
I am a novice at using VBA code. In the past, I have used a piece of code
that deletes a row if the row contains a particular string of text in one column. With a new worksheet I am developing, I need to use similar code, but I need to apply it more than once, to delete multiple rows containing several different pieces of text. I do not understand why, but the code works for the first criteria (i.e. successfully deletes the row containing the first text strinig), but it will not work on subsequent occasions. Here is the code I am attempting to use: Rem delete all rows on the active worksheet that have "Printed" in column AC. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("A:AJ"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "Printed:" Then 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 Rem delete all rows on the active worksheet that have "Employee Transactions & Totals" in column B. For Each cell In rng If (cell.Value) = "Employee Transactions & Totals" Then 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 As you can see, I want to delete rows that contains "Printed" in column AC, and then rows that contain "Employee Transactions & Totals" in column B. Actually, there will be several more sets of rows/text strings that I will want to delete. Just to give you the context, I am attempting to use a macro to re-format a Report that is delivered from one of our applications via email in Excel format. The report is pre-formatted and has a whole bunch of lines of header information that I want to remove. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to delete rows that contain text
Try
http://www.rondebruin.nl/delete.htm You can try this in the code example 'Or use this two examples if you want to check more values. ElseIf .Cells(Lrow, "A").Value = "jelle" Or _ .Cells(Lrow, "A").Value = "ron" Or _ .Cells(Lrow, "A").Value = "dave" Then .Rows(Lrow).Delete -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rod from Corrections" wrote in message ... I am a novice at using VBA code. In the past, I have used a piece of code that deletes a row if the row contains a particular string of text in one column. With a new worksheet I am developing, I need to use similar code, but I need to apply it more than once, to delete multiple rows containing several different pieces of text. I do not understand why, but the code works for the first criteria (i.e. successfully deletes the row containing the first text strinig), but it will not work on subsequent occasions. Here is the code I am attempting to use: Rem delete all rows on the active worksheet that have "Printed" in column AC. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("A:AJ"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "Printed:" Then 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 Rem delete all rows on the active worksheet that have "Employee Transactions & Totals" in column B. For Each cell In rng If (cell.Value) = "Employee Transactions & Totals" Then 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 As you can see, I want to delete rows that contains "Printed" in column AC, and then rows that contain "Employee Transactions & Totals" in column B. Actually, there will be several more sets of rows/text strings that I will want to delete. Just to give you the context, I am attempting to use a macro to re-format a Report that is delivered from one of our applications via email in Excel format. The report is pre-formatted and has a whole bunch of lines of header information that I want to remove. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to delete rows that contain text
Thanks so much, Ron! It worked like a charm!
"Ron de Bruin" wrote: Try http://www.rondebruin.nl/delete.htm You can try this in the code example 'Or use this two examples if you want to check more values. ElseIf .Cells(Lrow, "A").Value = "jelle" Or _ .Cells(Lrow, "A").Value = "ron" Or _ .Cells(Lrow, "A").Value = "dave" Then .Rows(Lrow).Delete -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rod from Corrections" wrote in message ... I am a novice at using VBA code. In the past, I have used a piece of code that deletes a row if the row contains a particular string of text in one column. With a new worksheet I am developing, I need to use similar code, but I need to apply it more than once, to delete multiple rows containing several different pieces of text. I do not understand why, but the code works for the first criteria (i.e. successfully deletes the row containing the first text strinig), but it will not work on subsequent occasions. Here is the code I am attempting to use: Rem delete all rows on the active worksheet that have "Printed" in column AC. Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("A:AJ"), ActiveSheet.UsedRange) For Each cell In rng If (cell.Value) = "Printed:" Then 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 Rem delete all rows on the active worksheet that have "Employee Transactions & Totals" in column B. For Each cell In rng If (cell.Value) = "Employee Transactions & Totals" Then 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 As you can see, I want to delete rows that contains "Printed" in column AC, and then rows that contain "Employee Transactions & Totals" in column B. Actually, there will be several more sets of rows/text strings that I will want to delete. Just to give you the context, I am attempting to use a macro to re-format a Report that is delivered from one of our applications via email in Excel format. The report is pre-formatted and has a whole bunch of lines of header information that I want to remove. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) | |||
Macro to delete rows with text cells | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |