Problem w/Chart Axis labeling with VBA

[Dennis]

I have a worksheet with 26 columns and 95 rows. I select and highlight columns
"v" and "z" Column "v" has the label BE and column "z" has the label PP.

I have a vba macro shown below that plots the xyscatterchart and labels the x
and y axis. the xyscatterchart always automatically chooses the z column (the
furthest righthand column) as the y data points and the v column as the x data
points. This data point selection happens no matter which column I select
first.(I select a column and then hold down the <ctrl> and select a 2nd column).

However, when I find the axis labels from the rng=selection, the rng(1).value is
always the label of the column I select first. If I select column z first and
then column v, the x axis label rgn(1).value =PP while the xyscatterchart makes
the z column values as the y axis values.

Since the selection of the 2 columns for the xyscatterchart is random, and the y
axis will always be the furthest righthand column chosen how do I find the
correct column labels without making sure I select the left most column first?

Also another weird thing is that when I select any 2 columns in the worksheet,
lNumRows=65536 and lNumCols=1. lNumRows should be 95 and lNumcol should be 2.
Why is this happening?

Thanks for any help with this problem.


==============vba code========================

Sub myScatterChart()

'On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim curwk As Worksheet
Dim SS As String
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

ii = 0
myName = ""

SS = ActiveSheet.Name
Set rng = Selection
With rng
lNumRows = .Rows.Count
lNumCols = .Columns.Count
MsgBox "rngrows=" & lNumRows & " rngcol=" & lNumCols & _
" rng(1)=" & rng(1).VALUE
End With

For Each myCell In rng
If Not IsNumeric(myCell.Value) Then
ii = ii + 1
myName = myName & myCell.Value
'MsgBox "ii=" & ii & " " & myCell.Value
If ii = 1 Then sColLabel1 = myCell.Value
If ii = 2 Then sColLabel2 = myCell.Value
End If
Next myCell

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)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = sColLabel2
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = sColLabel1
.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