Find the last cell in a column, get a count and paste the value to the top

Posted by Kurt on August 06, 2000 6:08 PM

How do I count any number of cells in a specified column, get a count and paste the count to the top of those cells.

Thanks in advance.

Posted by Kurt on August 07, 0100 7:31 AM

Thanks Celia!!

Have a great day!!

Posted by Celia on August 06, 0100 8:36 PM

Kurt
Assuming the range of cells is set in a declared variable called "myRange", to put the cell count of myRange in the cell immediately above the first cell of myRange :-

myRange(0, 1) = myRange.Rows.Count

myRange(0, 1) = myRange.Cells.Count

Celia

Posted by Celia on August 06, 0100 8:53 PM

Kurt
If you were also enquiring on how to set the range, the following will count the cells from A2 to the last non-empty cell in Column A, and put the count in cell A1 :-

Dim myRange As Range
Set myRange = Range(Range("A2"), Range("A65536").End(xlUp))
myRange(0, 1) = myRange.Cells.Count

Celia