Discussion Locked

Find last figure in a column of a table

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can anyone give me a clue to the following puzzle in Excel 97?

I have a .csv files containing a table which has several columns and rows of data. The number of rows could change and hence this is not fixed. The number of columns are fixed.

How can I find the last "occupied" cell of any one of columns in that table using VBA or any other feature?

The answer can only replate to Excel 97 and NOT above.

6 replies

by cleg

You can use the following which replicates the Ctrl+Arrow feature:

range("a1").end(xldown)

This returns a range of the last 'occupied' cell in the first column.

.end also accepts xlup, xltoright, xltoleft (I think thats the right syntax)

by atulkpatel

Sorry for the late reply - got very busy with work!!!

Thanks v. much for the answer. However, it only helps me partly. I think I was not clear enough with my oringinal question, so here it is.

Not only do I want to get to the end of last 'occupied' cell in a column but I want to find out the cell reference of that last 'occupied' cell in VBA.

Please, any help is most appriciated and I have increased the points to reflect this.

by cleg

With the VBA line

range("a1").end(xldown)

You can return a cell reference, address or whatever. The line itself returns a range object. Try the following:

Example

Sub GetCellProperties()
Dim OneCell As Range

Set OneCell = Range("a1").End(xlDown)
'Show last occupied Address
MsgBox OneCell.Address

'Make summary Formula in Row 1
OneCell.Offset(1, 0).Formula = _
"=SUM(" & Range("a1").Address & ":" & OneCell.Address & ")"

End Sub

This discussion is closed to new replies.