Delete Rows With no "F" in Them

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Hi All...

I would like to get a macro to work that would look through what is selected,
if it doest find a "F" anywhere in them, delete all rows in the selection that
have no "F"

I'll keep plugging away but any help might save me a few days, I've been stuck
on this one.

TIA...
Dennis
=======
 
Sub DelRows()

Dim r As Long
Dim Rng As Range
Dim RngRows As Long
Dim RngCols As Long
Dim Str1 As String

Application.ScreenUpdating = False

Str1 = "F"

Set Rng = Selection

RngRows = Rng.Rows.Count
RngCols = Rng.Columns.Count

For r = Rng.Rows.Count To 1 Step -1
With Rng.Cells(r, 1).Resize(1, RngCols)
Set s1 = .Find(Str1, LookIn:=xlValues)
If s1 Is Nothing Then
.EntireRow.Delete
End If
End With
Next r

Application.ScreenUpdating = True

End Sub

------------------------------------

or with a prompt for the string:-

Sub DelRows()

Dim r As Long
Dim Rng As Range
Dim RngRows As Long
Dim RngCols As Long
Dim Str1 As String

Application.ScreenUpdating = False

Str1 = InputBox("What String do you want rows to be deleted if they DON'T
contain it?")

Set Rng = Selection

RngRows = Rng.Rows.Count
RngCols = Rng.Columns.Count

For r = Rng.Rows.Count To 1 Step -1
With Rng.Cells(r, 1).Resize(1, RngCols)
Set s1 = .Find(Str1, LookIn:=xlValues)
If s1 Is Nothing Then
.EntireRow.Delete
End If
End With
Next r

Application.ScreenUpdating = True

End Sub
 
Need to watch for word wrap - The 'prompt for a String' line in the second
procedure should all be on one line.
 
My pleasure - Note the caution about the wordwrap potential on the second
prodecure - Not sure if it wrapped or not.
 
Yup got that, thanx.

What would be the opposite of 'Nothing'? Instead of 'If s1 Is Nothing Then' I
actually wanted to delete rows that had an 'F'.

Dennis
===========
 
Cheers Ron :-)

Did you see the thread in .public.excel entitled 'Automatic Backups' - Had me in
stitches

You got sent an email by mistake, replied to it to help, and then got asked to
keep your responses in the newsgroup - rotflmao!!!!!!!!!!

I know it wasn't quite as black and white as that, and the OP had her reasons
and did say it nicely, but it just seemed too funny for words when I first read
it. :-)
 
Back
Top