Delete rows that the first cell's text is "A"

[Greg :-)]

I have a large spreadsheet (41,000+ rows) that I would like an easy way to
delete rows that contain specific Network Letters that are in Column A.


Here is the first row of a very large POP dial up list. I'm tired of deleting
rows for networks I don't have access to. Is there easy way to setup a
macro/script to delete rows based on the text contained in column A?

Greg

Network Letter, Area Code, Number, City, State, Country, Tier, ISDN Offered,V92
Offered

A 205 6050101 Alabaster AL USA 1 N Y
....
E 317 7070800 Danville IN USA 1 N N
....
....
G 765 6798010 Glenwood IN USA 1 Y N
I 52 9999423904 Merida N/A MEX 3 N N

 

[Norman Jones]

Hi Greg,

Try:

'==============>>
Public Sub Tester()

Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim arr As Variant
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Columns(1)

arr = Array("A", "D", "G", "H") '<<===== CHANGE

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If Not IsError(Application.Match(rcell.Value, arr, 0)) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<==============

Change the Sheet name and the networkletters letters to suit your
requirements.

 

[Don Guillett]

sort or use data>filter>autofilter
then just delete. Record a macro if desired.