Delete Rows based on cell value

  • Thread starter Thread starter BrianWest83
  • Start date Start date
B

BrianWest83

Hi. I'm trying to create a macro that will delete rows where the
value in column E does not equal "ABC". I always have a different
number of rows of data so I can't use a fixed range. Also, rows 1-5
are my header rows so I don't want to touch them--so the macro should
start looking at column E in row 6 and continue until it hits a row
where column E is blank. Thanks in advance.
 
You could do following:

Sub DelRows()

Dim SH As Worksheet

Set SH = Worksheets("sheet1")

For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1
If LCase(SH.Cells(i, 5).Value) <> "abc" Then
SH.Rows(i).Delete
End If
Next i

End Sub

hth
Carlo
 
You could do following:

Sub DelRows()

Dim SH As Worksheet

Set SH = Worksheets("sheet1")

For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1
If LCase(SH.Cells(i, 5).Value) <> "abc" Then
SH.Rows(i).Delete
End If
Next i

End Sub

hth
Carlo
Click to expand...

For some reason that deleted ALL the rows including those with "ABC"
in column E. (It didn't delete the header rows though, which is good.)
 
that's weird.

what exactly do you look for?
is it really "ABC" or something else?
Right now i check if the lowercase of this cell is not equal "abc" so
if you have a space or something in it, it would delete it as well.

Carlo
 
that's weird.

what exactly do you look for?
is it really "ABC" or something else?
Right now i check if the lowercase of this cell is not equal "abc" so
if you have a space or something in it, it would delete it as well.

Carlo
Click to expand...

Yes, I am actually looking for "ABC" (This is for a list of TV shows
with each show getting a row. Column E is the network for each show
and I want to eliminate those on all networks except ABC. I'll also
use this to eliminate all but those on FOX, etc. but I figured I could
just swap out the network name in the macro.)

One thing I'm confused about (and I'm relatively inexperienced in
dealing with macros) is that the macro you wrote doesn't seem to
mention column E anywhere--is that correct?
 
Hi Brian

Ok....just wanted to make sure.
For your question about column E, you're right it doesn't say E,
because i use the columnNumber 5 (in rows 3 and 4) which works the
same way.

Try this macro and tell me, what output you get in the immediate
Window (if you don't see it, press Ctrl + G)

Sub DelRows()

Dim SH As Worksheet

Set SH = Worksheets("sheet1")

For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1
Debug.print SH.cells(i,5).value, LCase(SH.Cells(i, 5).Value) <>
"abc"
If LCase(SH.Cells(i, 5).Value) <> "abc" Then
SH.Rows(i).Delete
End If
Next i

End Sub

Is there a possibility of a space in front or after "ABC"?

Carlo
 
Hi Brian

Ok....just wanted to make sure.
For your question about column E, you're right it doesn't say E,
because i use the columnNumber 5 (in rows 3 and 4) which works the
same way.

Try this macro and tell me, what output you get in the immediate
Window (if you don't see it, press Ctrl + G)

Sub DelRows()

Dim SH As Worksheet

Set SH = Worksheets("sheet1")

For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1
Debug.print SH.cells(i,5).value, LCase(SH.Cells(i, 5).Value) <>
"abc"
If LCase(SH.Cells(i, 5).Value) <> "abc" Then
SH.Rows(i).Delete
End If
Next i

End Sub

Is there a possibility of a space in front or after "ABC"?

Carlo
Click to expand...

Thanks Carlo. That actually didn't work for me either. I can't
figure out exactly what's going wrong. I think I've found a way to
handle this using autofilter though. Thanks for all your help.
 
Thanks Carlo.  That actually didn't work for me either.  I can't
figure out exactly what's going wrong.  I think I've found a way to
handle this using autofilter though.  Thanks for all your help.- Hide quoted text -

- Show quoted text -
Click to expand...

Well....i didn't change the code...i just wanted to see what the
output would be.
But if you can do it with autofilter, no problem.

Cheers
Carlo
 
Back
Top