Why does rngDataSource.Rows.Count = 65536 when worksheet Rows=95?

[Dennis]

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/ChartsHowTo/QuickChartVBA.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]

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.

 

[Dennis]

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]

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>.)

 

[Dennis]

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]

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...

 

[Dennis]

Nope this didn't work.

When I selected columns X and V in that order, the code below produced column X
row 1 value and column Y(not V) row 1 value. It chooses the row 1 value of the
column next to the first selected column.

Set rngDataSource = Intersect(ActiveSheet.UsedRange, Selection)
MsgBox rngDataSource(1).Value & " " & rngDataSource(1).Offset(0, 1).Value

It seems the only way to do this is with a For loop?

Thanks

Dennis

 

[Dave Peterson]

I'm not sure why the second column's label isn't in the same row as the first
column's label.

I've read your code and I don't quite understand the way the data would be laid
out.

But if looping works, that's ok with me.

 

[Dave Peterson]

So you have multiple columns in your selection and you want the header from the
second column of that selection?

(I still don't know what you want to do.)

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range
Dim my2ndHeader As Range

Set myRng = Nothing
With Worksheets("sheet1")
On Error Resume Next
Set myRng = Intersect(.UsedRange, Selection)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "Please select cells in the used range."
Exit Sub
End If

Set my2ndHeader = Nothing

Select Case myRng.Areas.Count
Case Is > 2
MsgBox "Please select no more than 2 areas"
Case Is = 1
If myRng.Columns.Count <> 2 Then
MsgBox "Please select two columns"
Else
Set my2ndHeader = myRng.Cells(1, 1).Offset(0, 1)
End If
Case Is = 2
If myRng.Areas(1).Row <> myRng.Areas(2).Row Then
MsgBox "Please start in the same row"

ElseIf myRng.Areas(1).Rows.Count _
<> myRng.Areas(2).Rows.Count Then
MsgBox "Please make each area in the" & _
" selection the same number of rows"

ElseIf myRng.Areas(1).Columns.Count _
<> myRng.Areas(2).Columns.Count Then
MsgBox "Please make each area the same number of columns"

ElseIf myRng.Areas(1).Columns.Count <> 1 Then
MsgBox "Please select one column in each area"

Else
Set my2ndHeader = myRng.Areas(2).Cells(1, 1)

End If
End Select

If my2ndHeader Is Nothing Then
'do nothing
Else
MsgBox my2ndHeader.Address & vbLf & my2ndHeader.Value
End If

End Sub

If this doesn't help, how about just describing what you're trying to do.

 

[Dennis]

Thanks Dave!

myRng.Areas(2).Cells(1, 1) is just what I was looking for. I did not know how
to address the first cell in the 2nd selected column. I didn't know that
"Areas" existed.

What I was trying to do is simplify the code for finding the first cell, which
is a column header, in the 2nd selected column.

For the total code the user(that's me) selects any two columns from a worksheet,
clicks on an icon and a xyScatterChart is displayed of those columns with the
axis's labeled with the first cells from each of the two selected columns. I
gave the code for this macro in my previous post on 7/19/05 entitled "Problem
w/Chart Axis Labeling with VBA"

One problem remains. The xyScatterChart always chooses the right most column
selected as it's Y axis no matter whether it was selected first or last. When
the user selects the rightmost column first and then selects a column to the
left of this column the axis labels will be reversed. Is there anyway to tell
which column is furthest to the right? That is the selected column number?

Thanks.

Dennis

 

[Dennis]

Here is the solution to the last selected column problem for chart axis labeling
described below.

sColLabel1 = rngDataSource.Areas(1).Cells(1, 1)
sColLabel2 = rngDataSource.Areas(2).Cells(1, 1)
If rngDataSource.Areas(2).Column < rngDataSource.Areas(1).Column Then
sColLabel1 = rngDataSource.Areas(2).Cells(1, 1)
sColLabel2 = rngDataSource.Areas(1).Cells(1, 1)
End If

This code will make sColLabel2 equal to the rightmost column label even if the
rightmost selected column was selected first.

Dennis

 

[Dave Peterson]

That seems like a nice approach.

But be careful. If I select A1:B10, I have two columns, but only one area.

But if I select A1:A10, then ctrl-click on B1:B10, I have two areas.

So I would think you'd have to watch out for that, too.