What is Range For Chart AutoFilter Range VBA?

[Dennis]

Below Is a snippet of code for a VBA Macro to plot an xyScatter chart on any
selected two columns of a spreadsheet. I've modified the code but the original
comes from Jon Peltiers' excellent site

When I use the sutofilter on the spreadsheet, this code will not plot the
autofiltered columns correctly.

How do I set the intersect range(rng) so that only the autofiltered visible will
plot?

Thanks for any help.

=======xyScatter Plot VBA below=============
Sub myScatterChart()

'On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim curwk As Worksheet
Dim myCell As Range
Dim rng As Range
Dim myName As String, sColLabel1 As String, sColLabel2 As String
Dim ChartName As String
Dim ii As Long
Dim lNumRows As Long
Dim lNumCols As Long
Dim x As Long

'x = ActiveSheet.UsedRange.Rows.Count
ii = 0
myName = ""

Set rng = Intersect(ActiveSheet.UsedRange, Selection)

Select Case rng.Areas.Count
Case Is = 1
sColLabel2 = rng.Areas(1).Cells(1, 1)
sColLabel1 = "Number"
Case Is = 2
sColLabel1 = rng.Areas(1).Cells(1, 1)
sColLabel2 = rng.Areas(2).Cells(1, 1)
If rng.Areas(2).Column < rng.Areas(1).Column Then
sColLabel1 = rng.Areas(2).Cells(1, 1)
sColLabel2 = rng.Areas(1).Cells(1, 1)
End If
End Select

myName = sColLabel1 & sColLabel2

Charts.Add

ChartName = ActiveChart.Name

If myName <> "" And Not myNameExists(myName) Then ActiveChart.Name = myName

With ActiveChart
.ChartType = xlXYScatter
.SetSourceData Source:=rng, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.Move After:=Sheets(Sheets.Count)
.HasTitle = True
.ChartTitle.Characters.Text = sColLabel1 & " vs " & sColLabel2
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = sColLabel1
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = sColLabel2
.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With .Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward _
:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
.PlotArea.Select
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub