Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why does rngDataSource.Rows.Count = 65536 when worksheet Rows=95?
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 wrote: 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 |
#8
|
|||
|
|||
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. wrote: 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 wrote: 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 |
#9
|
|||
|
|||
I have a spreadsheet with 26 columns and 94 rows. The first row consists of
headers(labels) for each column of numbers. Let's call the column labels "A" through "Z" If I use the below code and select columns V and Z in that order the MsgBox gives me the following values: iDataRowsCt=94 kk=95 sColLabel1=V sColLabel2=Z rngDataSource(1)=V rngDataSource(iDataRowsCt+1)=nothing When I tried rngDataSource(1,2) then rngDataSource(1,2)=U even though column U was not selected. The "For Each MyCell" loop seems to skip the unselected columns and find the selected column labels. However, I can't seem to access the 2nd selected columns label directly with array numbers because it displays the unselected column values as shown in the output above. How do you address only the selected column values, namely the 2nd selected column's label? Thanks Dennis ========================Code====================== ============= Set rngDataSource = Intersect(ActiveSheet.UsedRange, Selection) iDataRowsCt = rngDataSource.Rows.Count Dim mycell As Range Dim ii As Long, jj As Long, kk As Long Dim myName As String, sColLabel1 As String, sColLabel2 As String ii = 0 For Each mycell In rngDataSource jj = jj + 1 If Not IsNumeric(mycell.Value) Then ii = ii + 1 kk = jj myName = myName & mycell.Value If ii = 1 Then sColLabel1 = mycell.Value If ii = 2 Then sColLabel2 = mycell.Value End If Next mycell MsgBox "idatarowsct=" & iDataRowsCt & vbLf & _ "kk=" & kk & " " & sColLabel1 & " " & sColLabel2 & vbLf & _ rngDataSource(1).Value & " " & rngDataSource(iDataRowsCt+1).Value Dave Peterson wrote: 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. wrote: 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 wrote: 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 |
#10
|
|||
|
|||
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. wrote: I have a spreadsheet with 26 columns and 94 rows. The first row consists of headers(labels) for each column of numbers. Let's call the column labels "A" through "Z" If I use the below code and select columns V and Z in that order the MsgBox gives me the following values: iDataRowsCt=94 kk=95 sColLabel1=V sColLabel2=Z rngDataSource(1)=V rngDataSource(iDataRowsCt+1)=nothing When I tried rngDataSource(1,2) then rngDataSource(1,2)=U even though column U was not selected. The "For Each MyCell" loop seems to skip the unselected columns and find the selected column labels. However, I can't seem to access the 2nd selected columns label directly with array numbers because it displays the unselected column values as shown in the output above. How do you address only the selected column values, namely the 2nd selected column's label? Thanks Dennis ========================Code====================== ============= Set rngDataSource = Intersect(ActiveSheet.UsedRange, Selection) iDataRowsCt = rngDataSource.Rows.Count Dim mycell As Range Dim ii As Long, jj As Long, kk As Long Dim myName As String, sColLabel1 As String, sColLabel2 As String ii = 0 For Each mycell In rngDataSource jj = jj + 1 If Not IsNumeric(mycell.Value) Then ii = ii + 1 kk = jj myName = myName & mycell.Value If ii = 1 Then sColLabel1 = mycell.Value If ii = 2 Then sColLabel2 = mycell.Value End If Next mycell MsgBox "idatarowsct=" & iDataRowsCt & vbLf & _ "kk=" & kk & " " & sColLabel1 & " " & sColLabel2 & vbLf & _ rngDataSource(1).Value & " " & rngDataSource(iDataRowsCt+1).Value Dave Peterson wrote: 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. wrote: 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 wrote: 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 |
#11
|
|||
|
|||
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 Dave Peterson wrote: 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. wrote: I have a spreadsheet with 26 columns and 94 rows. The first row consists of headers(labels) for each column of numbers. Let's call the column labels "A" through "Z" If I use the below code and select columns V and Z in that order the MsgBox gives me the following values: iDataRowsCt=94 kk=95 sColLabel1=V sColLabel2=Z rngDataSource(1)=V rngDataSource(iDataRowsCt+1)=nothing When I tried rngDataSource(1,2) then rngDataSource(1,2)=U even though column U was not selected. The "For Each MyCell" loop seems to skip the unselected columns and find the selected column labels. However, I can't seem to access the 2nd selected columns label directly with array numbers because it displays the unselected column values as shown in the output above. How do you address only the selected column values, namely the 2nd selected column's label? Thanks Dennis ========================Code====================== ============= Set rngDataSource = Intersect(ActiveSheet.UsedRange, Selection) iDataRowsCt = rngDataSource.Rows.Count Dim mycell As Range Dim ii As Long, jj As Long, kk As Long Dim myName As String, sColLabel1 As String, sColLabel2 As String ii = 0 For Each mycell In rngDataSource jj = jj + 1 If Not IsNumeric(mycell.Value) Then ii = ii + 1 kk = jj myName = myName & mycell.Value If ii = 1 Then sColLabel1 = mycell.Value If ii = 2 Then sColLabel2 = mycell.Value End If Next mycell MsgBox "idatarowsct=" & iDataRowsCt & vbLf & _ "kk=" & kk & " " & sColLabel1 & " " & sColLabel2 & vbLf & _ rngDataSource(1).Value & " " & rngDataSource(iDataRowsCt+1).Value |
#12
|
|||
|
|||
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 wrote: 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 |
#13
|
|||
|
|||
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. wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart -- How to chart 65536 values in 1 series? | About this forum | |||
Excel should accomodate more number of rows than 65536, to load l. | Excel Discussion (Misc queries) | |||
How can I open a large Excel file with more than 65536 rows? | Excel Discussion (Misc queries) | |||
More than 65536 to new sheet | Excel Worksheet Functions | |||
increasing the number of rows of a worksheet beyond 65536? | Excel Discussion (Misc queries) |