Global Report only need USA Data . . .

  • Thread starter Thread starter Rachel Costanza
  • Start date Start date
R

Rachel Costanza

Hello,

We are moving to global reports and they produce data for areas all over the
world. For US clients who do not want to see data outside of the USA I have
to manually delete data for UK, France, Dublin, China, Tokyo etc. and I was
wondering if there was a macro I could run to remove all of the rows with a
location outside of the USA while still leaving the USA data element of the
report in tact.

Any insight is appreciated!

Rachel
 
Sub deleterows()
Dim rngColumnA As Range
Const sUSA As String = "USA"
Dim i As Long
'Change rngColumnA to the Column that has USA
Set rngColumnA = _
ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
'Work backwards from bottom to top when deleting rows
Application.ScreenUpdating = False
With rngColumnA
For i = .Rows.Count To 1 Step -1
If .Cells(i) <> sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
 
Hi,

Suppose you have a column called Country. Select the data and choose Data,
Filter, AutoFilter, open the filter dropdown in the Country columns top cell
and choose Custom, from the first drop down pick not equal to and on the top
right box enter United States or whatever you use to designate US customers.

When you click OK all non US will be displayed, select all the visible data
and press delete. Choose Data, Filter and uncheck AutoFilter. Only the US
is left.

If this helps, please click the Yes button.
 
Back
Top