deleting multiple rows with multiple criteria
 

Hello: looking for code to a macro that will delete rows based on multiple
critera. For example, worksheet has 10,000 rows of data; however, need to
delete any row that begin with (no quotes):

"ACTH"
"#55"
"-----"

Some rows may have ACTH-4577, but still need to be delted b/c the have the
"ACTH" at the beginning.

Thanks!

deleting multiple rows with multiple criteria
 

Hi Pablo,

1. Copy the below code in to a module:

Sub KillRows()
Dim c1, c2
Dim strRange As Range, killRange As Range
Set strRange = Selection
With Sheet1.UsedRange
Set killRange = Sheet1.Cells(.Cells.Rows.Count + 1, 1)
For Each c1 In strRange.Cells
For Each c2 In .Cells
If (Not IsEmpty(c1)) And InStr(1, Trim(c2.Value),
Trim(c1.Value)) 0 Then
If Application.Intersect(c2.EntireRow, killRange) Is Nothing
Then
Set killRange = Union(killRange, c2)
End If
End If
Next c2
Next c1
End With
If killRange.Cells.Count = 1 Then
MsgBox "None of the enterted text strings were found!", vbCritical
Else
killRange.EntireRow.Delete
End If
Set killRange = Nothing
Set strRange = Nothing
End Sub

2. I assumed above that the name of sheet containing your rows is "Sheet1",
Change it to name of your sheet.

3. On another sheet (if necessary insert a blank sheet), enter the text
string to be searched
in to seperate cells.
e.g.: in A1 enter ACTH, in A2 enter #55, in A3 enter ---- .
Please note you must do it either in a seperate sheet OR in the same sheet
BELOW the last used row.

4. Select all the cells where you entered the search text as in step 3
above.

5. Run the macro KillRows

Sharad

"Pablo" wrote in message
...
Hello: looking for code to a macro that will delete rows based on multiple
critera. For example, worksheet has 10,000 rows of data; however, need to
delete any row that begin with (no quotes):

"ACTH"
"#55"
"-----"

Some rows may have ACTH-4577, but still need to be delted b/c the have the
"ACTH" at the beginning.

Thanks!

deleting multiple rows with multiple criteria
 

Thanks for the help. However, I've tried to run this a couple times and
keep getting the message, "Object Required". Then the debugger
highlights this:

Set killRange = CCDA.Cells(.Cells.Rows.Count + 1, 1)


Also, is there a way to identify what row the macro will start? For
example, if I have the header "Apples from Washington" starting in row
1 and repeated every 75th row, can I somehow tell the macro to ignore
row 1? (ie-highlight the range I want the macro to run against?)




Sharad Naik wrote:
Hi Pablo,

1. Copy the below code in to a module:

Sub KillRows()
Dim c1, c2
Dim strRange As Range, killRange As Range
Set strRange = Selection
With Sheet1.UsedRange
Set killRange = Sheet1.Cells(.Cells.Rows.Count + 1, 1)
For Each c1 In strRange.Cells
For Each c2 In .Cells
If (Not IsEmpty(c1)) And InStr(1, Trim(c2.Value),
Trim(c1.Value)) 0 Then
If Application.Intersect(c2.EntireRow, killRange) Is
Nothing
Then
Set killRange = Union(killRange, c2)
End If
End If
Next c2
Next c1
End With
If killRange.Cells.Count = 1 Then
MsgBox "None of the enterted text strings were found!",
vbCritical
Else
killRange.EntireRow.Delete
End If
Set killRange = Nothing
Set strRange = Nothing
End Sub

2. I assumed above that the name of sheet containing your rows is
"Sheet1",
Change it to name of your sheet.

3. On another sheet (if necessary insert a blank sheet), enter the
text
string to be searched
in to seperate cells.
e.g.: in A1 enter ACTH, in A2 enter #55, in A3 enter ---- .
Please note you must do it either in a seperate sheet OR in the same
sheet
BELOW the last used row.

4. Select all the cells where you entered the search text as in step
3
above.

5. Run the macro KillRows

Sharad

"Pablo" wrote in message
...
Hello: looking for code to a macro that will delete rows based on
multiple
critera. For example, worksheet has 10,000 rows of data; however,
need to
delete any row that begin with (no quotes):

"ACTH"
"#55"
"-----"

Some rows may have ACTH-4577, but still need to be delted b/c the
have the
"ACTH" at the beginning.

Thanks!

deleting multiple rows with multiple criteria
 

Thanks for the help. However, I've tried to run this a couple times and
keep getting the message, "Object Required". Then the debugger
highlights this:


Set killRange = CCDA.Cells(.Cells.Rows.Count + 1, 1)


Also, is there a way to identify what row the macro will start? For
example, if I have the header "Apples from Washington" starting in row
1 and repeated every 75th row, can I somehow tell the macro to ignore
row 1? (ie-highlight the range I want the macro to run against?)






"Sharad Naik" wrote in message
...
Hi Pablo,

1. Copy the below code in to a module:

Sub KillRows()
Dim c1, c2
Dim strRange As Range, killRange As Range
Set strRange = Selection
With Sheet1.UsedRange
Set killRange = Sheet1.Cells(.Cells.Rows.Count + 1, 1)
For Each c1 In strRange.Cells
For Each c2 In .Cells
If (Not IsEmpty(c1)) And InStr(1, Trim(c2.Value),
Trim(c1.Value)) 0 Then
If Application.Intersect(c2.EntireRow, killRange) Is
Nothing Then
Set killRange = Union(killRange, c2)
End If
End If
Next c2
Next c1
End With
If killRange.Cells.Count = 1 Then
MsgBox "None of the enterted text strings were found!", vbCritical
Else
killRange.EntireRow.Delete
End If
Set killRange = Nothing
Set strRange = Nothing
End Sub

2. I assumed above that the name of sheet containing your rows is
"Sheet1",
Change it to name of your sheet.

3. On another sheet (if necessary insert a blank sheet), enter the text
string to be searched
in to seperate cells.
e.g.: in A1 enter ACTH, in A2 enter #55, in A3 enter ---- .
Please note you must do it either in a seperate sheet OR in the same sheet
BELOW the last used row.

4. Select all the cells where you entered the search text as in step 3
above.

5. Run the macro KillRows

Sharad

"Pablo" wrote in message
...
Hello: looking for code to a macro that will delete rows based on
multiple
critera. For example, worksheet has 10,000 rows of data; however, need to
delete any row that begin with (no quotes):

"ACTH"
"#55"
"-----"

Some rows may have ACTH-4577, but still need to be delted b/c the have
the
"ACTH" at the beginning.

Thanks!