Delete row IF left character is in list

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

Hi All........

If you please, is it possible to highlight a column, and then have a macro
to delete all rows whose leftmost character in that column is equal to a
character in a Range named DeleteList?

TIA for any help....

Vaya con Dios,
Chuck, CABGx3
 
Hi
try the following macro

Sub delete_rows()
Dim rng As Range
Dim lastrow As Long
Dim row_index As Long
Dim lookup_rng As Range

Set rng = Selection
If rng.Columns.count > 1 Then
MsgBox "Only one column allowed for selection"
Exit Sub
End If
Set lookup_rng = Range("DeleteList")

lastrow = ActiveSheet.Cells(Rows.count, rng.Column).End(xlUp).row
For row_index = lastrow To 1 Step -1
If Application.WorksheetFunction.CountIf(lookup_rng, _
Left(Cells(row_index, rng.Column).Value, 1)) > 0 Then
Cells(row_index, rng.Column).EntireRow.Delete
End If
Next
End Sub
 
Sub DeleteIfInList()
Dim varr As Variant
Dim j As Long, i As Long
Dim rng As Range
Dim sChar As String
varr = Range("DeleteList").Value
For j = LBound(varr, 1) To UBound(varr, 1)
varr(j, 1) = LCase(varr(j, 1))
Next
Set rng = Cells(Rows.Count, ActiveCell.Column).End(xlUp)
For i = rng.Row To 1 Step -1
sChar = LCase(Left(Cells(i, ActiveCell.Column), 1))
For j = LBound(varr, 1) To UBound(varr, 1)
If sChar = varr(j, 1) Then
Cells(i, 1).EntireRow.Delete
Exit For
End If
Next
Next
End Sub
 
Thanks Frank............

This one worked too fine...........it also deleted my "DeleteList"
<G>..........I guess I just have to be careful where I put it. I moved it
and all is fine.

Thanks again,
Vaya con Dios,
Chuck, CABGx3
 
Hi Tom..........

Thanks for the response but I'm having trouble with this one...........I
keep getting a "Run-time error "13" Type mismatch" errormessage........ and
other times I get Run-time error "1004" Application-defined or
object-defined error" errormessage...........I don't know what either
mean............it's probably the way I copied it off or something, but I
tried several times. These things are 'way over my head so I don't even
know how else to describe it.

Vaya con Dios,
Chuck, CABGx3
 
NOW you tell me..........I thought the thing was going NUTS
<smile>..............anyway, as it turns out, I find now that the list I
want to keep is much shorter than the list I want to
delete<blush>..........how could I modify the code to turn the thing around
and make it delete all rows EXCEPT the ones that are on the DeleteList?
I've tried, but I can't figure it out............

Vaya con Dios,
Chuck, CABGx3


bvvvvvvvvvvvvvvvvvvvvvvghhhcv
 
Hi
quite simple. Replace the line
If Application.WorksheetFunction.CountIf(lookup_rng, _
Left(Cells(row_index, rng.Column).Value, 1)) > 0 Then

with
If Application.WorksheetFunction.CountIf(lookup_rng, _
Left(Cells(row_index, rng.Column).Value, 1)) = 0 Then
 
Cool, that did the trick..............thank you kind Sir, you make it look
easy.


Vaya con Dios,
Chuck, CABGx3
 
Back
Top