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

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

Thanks Dave the Intersect code worked great. I always wondered how to use the
UsedRange. My previous attempts all resulted in errors.

Is there a way to get the first value of each selected column when the columns
are selected randomly?

Jon's chart code below only works if the columns are right next to each other.

Dennis

Dave Peterson wrote:

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

You could use the activecell.

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

You could use the first cell in the selection.

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

Depending on how weird you (or your users are, it could be different columns
<vbg.)

wrote:

Thanks Dave the Intersect code worked great. I always wondered how to use the
UsedRange. My previous attempts all resulted in errors.

Is there a way to get the first value of each selected column when the columns
are selected randomly?

Jon's chart code below only works if the columns are right next to each other.

Dennis

Dave Peterson wrote:

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

Thanks again Dave.

My columns consist of all numbers accept for the label in row 1.

I know I can get the first selected column's label by using rng(1).value .
However, in order to find the 2nd column's label I had to use a For loop and
test as in :
sColLabel1=rng(1).value
For Each myCell In rng
If Not IsNumeric(myCell.Value) Then
ii = ii + 1
myName = myName & myCell.Value
If ii = 2 Then sColLabel2 = myCell.Value
End If
Next myCell

Is there a one line faster way to get the second column label?

Thanks.

Dennis

Dave Peterson wrote:

You could use the activecell.

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

You could use the first cell in the selection.

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

Depending on how weird you (or your users are, it could be different columns
<vbg.)

wrote:

Thanks Dave the Intersect code worked great. I always wondered how to use the
UsedRange. My previous attempts all resulted in errors.

Is there a way to get the first value of each selected column when the columns
are selected randomly?

Jon's chart code below only works if the columns are right next to each other.

Dennis

Dave Peterson wrote:

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

How about:

msgbox rng(1).offset(0,1).value

(first cell of the range, same row, over one column to the right.)

If I understood correctly, that is...

wrote:

Thanks again Dave.

My columns consist of all numbers accept for the label in row 1.

I know I can get the first selected column's label by using rng(1).value .
However, in order to find the 2nd column's label I had to use a For loop and
test as in :
sColLabel1=rng(1).value
For Each myCell In rng
If Not IsNumeric(myCell.Value) Then
ii = ii + 1
myName = myName & myCell.Value
If ii = 2 Then sColLabel2 = myCell.Value
End If
Next myCell

Is there a one line faster way to get the second column label?

Thanks.

Dennis

Dave Peterson wrote:

You could use the activecell.

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

You could use the first cell in the selection.

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

Depending on how weird you (or your users are, it could be different columns
<vbg.)

wrote:

Thanks Dave the Intersect code worked great. I always wondered how to use the
UsedRange. My previous attempts all resulted in errors.

Is there a way to get the first value of each selected column when the columns
are selected randomly?

Jon's chart code below only works if the columns are right next to each other.

Dennis

Dave Peterson wrote:

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