Selection.count isnt working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am testing a macro and I cant figure out what I'm doing wrong. I have
named a range, dvec, that contains contiguous data. I want to pass the
number of data points, n, to the array cvec(n). To test this I inserted the
MsgBox line. When I run this macro, I get the number of rows in the range
dvec rather than the count of data points. What am I doing wrong? I have
made sure all blank cells in the range are clear.

Option Base 1
Sub SelectCount()
'Re-Dim data array cvec
Dim n, cvec
Sheets("Sheet2").Range("dvec").Select
n = Selection.Count
ReDim cvec(n)
MsgBox "The size of vector cvec is " & n
End Sub

TIA

Steve H
 
Steve,

I am not sure what you are referring to when you say points, but I get the
number of cells, not the number of rows, as I would have expected.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I agree with Bob. If this is a single column range, the number of cells,
number of rows and number of data points should all agree. It is unclear
how data points differ from the number of cells/rows. If it is multicolumn
(single area), then you just want the number of rows

n = Selection.Rows.count

However, if the end result is to pick up the data in the range to the array

Option Base 1
Sub SelectCount()
Dim n, cvec
cvec = Range("dvec").Value

MsgBox "The size of vector cvec is 1 x " & UBound(cvec, 1) _
& ", 1 x " & UBound(cvec, 2)
sStr = ""
For i = 1 To UBound(cvec, 1)
For j = 1 To UBound(cvec, 2)
sStr = sStr & cvec(i, j) & ", "
Next
sStr = sStr & vbNewLine
Next
MsgBox sStr
End Sub

Using this method to pick up your data, the variable must be a variant, and
it will produce a 2-D array even if you are picking up a single column or
single row.

A msgbox is limited to 255 characters I believe, so if you array is large,
the demo will not display its elements.
 
Back
Top