When you select the whole column, you get all the cells in that column.

Maybe you could limit it to just the usedrange (which may not be what you expect
either!):

Dim myRng As Range
Set myRng = Intersect(ActiveSheet.UsedRange, Selection)
MsgBox Selection.Address & vbLf & myRng.Address

Debra Dalgleish has some techniques to reset that last used cell if it's way
past what you think it should be.

http://www.contextures.com/xlfaqApp.html#Unused



Another way is to be more specific with your range.

dim myRng as range
with activesheet
set myrng = .range("b3",.cells(.rows.count,"B").end(xlup))
end with

This includes B3 through the last used cell in column B.





wrote:

I took the below example from Jon Peltier's site at http://peltiertech.com/

I have a worksheet with 26 columns and 95 rows. The first row consists of labels
and all other rows are numbers. When I highlight any three columns that are not
next to each other I get iDataRowsCt=65536 and iDataColsCt=1.

When I highlight 4 column that are next to each other I get iDataRowsCt=65536
and iDataColsCt=4.

Why is the number of rows=65536 and not equal to 95?

Why is he number of columns=1 when the selected columns are not next to each
other?

==========From http://peltiertech.com/Excel/ChartsH...kChartVBA.html
================================================== ====================
Sub MultiX_OneY_Chart()

Dim rngDataSource As Range
Dim iDataRowsCt As Long
Dim iDataColsCt As Integer
Dim iSrsIx As Integer
Dim chtChart As Chart
Dim srsNew As Series

If Not TypeName(Selection) = "Range" Then
'' Doesn't work if no range is selected
MsgBox "Please select a data range and try again.", _
vbExclamation, "No Range Selected"
Else
Set rngDataSource = Selection
With rngDataSource
iDataRowsCt = .Rows.Count
iDataColsCt = .Columns.Count
MsgBox "idataRowsCt=" & iDataRowsCt & " iDataColsCt=" & iDataColsCt
End With

--

Dave Peterson