How do I delete some rows while keeping others

[Daryl Timm]

Every day I receive a list like the following:

9 - spice

7 - aurora

5 - dallas

4 - tulsa

4 - frank

4 - eville

3 - waco

3 - pont

2 - warren

2 - ripon

2 - quincy

2 - lakep

2 - gulf

2 - gretna

2 - george

2 - birm

1 - wmemph

1 - winch

1 - wfalls

1 - sulliv

1 - sioux



This list includes many entries that don't concern me, but a few of them do.
The numbers preceding the "- text" change every day, but the text does not
change. I would like to be able to delete the rows that don't concern me,
how would I go about doing this?



Thanks!

 

[crferguson]

There's a few ways to approach this, but a simple one would go
something like:

Public Sub DeleteRows()
Range("A1").Select

Do Until ActiveCell.Value = Empty
If ActiveCell.Value = "Value I don't want" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ElseIf ActiveCell.Value = "Another value I dont want" Then
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

 

[Guest]

First off we need to know what concerns you. After that the problem is just
one of mechanics. Do you wnat to keep certain numbers, or certain cities or
some combination there of???

 

[crferguson]

My mistake, I missed one line of code in that example. It should be as
follows:

Public Sub DeleteRows()
Range("A1").Select


Do Until ActiveCell.Value = Empty
If ActiveCell.Value = "Value I don't want" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ElseIf ActiveCell.Value = "Another value I dont want" Then
ActiveCell.EntireRow.Delete shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

 

[Daryl Timm]

I want to delete the row where the city does not concern me.

 

[Guest]

Give this a try... You will need to define which sheet you want to delete the
rows from (Sheet1 in my example) and the cities. I am deleting dallas and
tulsa...

Public Sub DeleteCities()
Call DeleteCity("dallas")
Call DeleteCity("tulsa")
End Sub

Sub DeleteCity(ByVal City As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = Sheets("Sheet1") 'Change This
Set rngToSearch = wks.Range("A:A") 'Possibly Change this
Set rngFound = rngToSearch.Find(What:=City, _
Lookat:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Delete
End If
End Sub

 

[Daryl Timm]

Works like a dream!!!

Thanks Jim

Give this a try... You will need to define which sheet you want to delete
the
rows from (Sheet1 in my example) and the cities. I am deleting dallas and
tulsa...

Public Sub DeleteCities()
Call DeleteCity("dallas")
Call DeleteCity("tulsa")
End Sub

Sub DeleteCity(ByVal City As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = Sheets("Sheet1") 'Change This
Set rngToSearch = wks.Range("A:A") 'Possibly Change this
Set rngFound = rngToSearch.Find(What:=City, _
Lookat:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Delete
End If
End Sub