Selecting data with a specfic value only

[ATanker62]

Hello all,
Excel 2007
I obtain a dump of data into an Excel spreadsheet. I cannot change the
requested data from the data dump, it simply gives me all the transactions
for every client. I wish to extract from this dump of data only the
information for one specfic client. I would like to write a macro to do
this. The client name appears in the column AG also called sitename. Any
ideas, thanks

 

[Rick Rothstein]

Give this macro a try (change the SearchName constant to the name you want
to keep)...

Sub DumpData()
Dim X As Long
Dim LastRow As Long
Const SearchName As String = "Bob"
On Error GoTo Whoops
Application.ScreenUpdating = False
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row
For X = LastRow To 1 Step -1
If StrComp(.Cells(X, "AG").Value, SearchName, vbTextCompare) <> 0 Then
.Rows(X).Delete
End If
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub

 

[Phillip M. Feldman]

Since this code deletes the rows that don't match, I recommend making a copy
of the worksheet before running this.

 

[Rick Rothstein]

Excellent suggestion! Thanks for posting it.

 

[ATanker62]

Thank you it works great

Give this macro a try (change the SearchName constant to the name you want
to keep)...

Sub DumpData()
Dim X As Long
Dim LastRow As Long
Const SearchName As String = "Bob"
On Error GoTo Whoops
Application.ScreenUpdating = False
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row
For X = LastRow To 1 Step -1
If StrComp(.Cells(X, "AG").Value, SearchName, vbTextCompare) <> 0 Then
.Rows(X).Delete
End If
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub