How to retrieve a column number and apply it to define a range?

  • Thread starter Thread starter Frank Krogh
  • Start date Start date
F

Frank Krogh

I am using the function COUNTA(A1:A65536) to retrieve the number of rows in
a imported dataset.

How do I use this number to make a definition in the SUM function?

This method does not seems to work
=CONCATENATE("=RANGE(A1:A";COUNTA(A:A);")")

Thanks for any suggestion

Frank Krogh
 
Frank,

Along the lines of your question,

=SUM(INDIRECT("A1:A" & COUNTA(A:A)))

will work, but you can also simply use

=SUM(A:A)

HTH,
Bernie
MS Excel MVP
 
Frank,

I would suggest that counting the entries is no good as there may be blanks.
As an example, if A1 had a number, A2 was blank, ad A3 had data, COUNTA(A:A)
returns 2 so your sum would be A1:A2, whereas you need A1:A3. So you need a
much more complex formula to derive the last row with data in it.

But why not just us =SUM(A:A)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the suggestion.

But provided there are no blanks, how do I assign the number of rows
retrieved by SET reportRows = COUNTA("A:A), when applying say
WORKSHEETS("Sheet1").Range("1: reportRows").SELECT ?


Frank Krogh
 
VBA! Why didn't you say?

In VBA there are two ways

reportRows = Cells(Rows.Count,"A").End(xlUp).Row

or using the built-in worksheet functions

reportRows = worksheetfunction.CountA(Columns(1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry that I failed to explain :-)

How do I apply the "reportRows" variable in order to select a cell range in
VBA (also in a cell formula)?


Frank Krogh
 
Create a function?

Function NumUsedRows (rng as range)

NumUsedRows = Cells(Rows.Count,rng(1,1).column).End(xlUp).Row
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top