MrExcel.com - Your One Stop for Excel Tips & Solutions

Using last used cell in column within formula

Posted by Barry Ward on August 13, 2001 3:12 AM


Aladin gave me this formula the other day that locates the last cell used in a column (thanks by the way)

=address(match(1e+30,a,a),column(a1))

but how do I use the result to determine a range i.e range("a3: Result of Fomula")?


Posted by Aladin Akyurek on August 13, 2001 5:32 AM

Barry,

It is:

="$A$3"&":"&ADDRESS(MATCH(1E+30,A:A),COLUMN(A1))

Caveat: the row number of the last cell must be at least 3.

Aladin

==========


Posted by Barry Ward on August 13, 2001 7:46 AM

I can't get this to work, I would guess it's something to do with my syntax since I know nothing of VBA, can you help?


'Calculates last used cell in column A and copies it

Sheets("results").Select
Range("d2").Select
ActiveCell.Formula = "=address(match(1e+30,a:a),column(a1)) "
Range("$A$3"&":"&address(match(1e+30,a:a),column(a1))).Select
Selection.Copy

Posted by Ivan F Moala on August 13, 2001 9:05 PM

Barry try

'Calculates last used cell in column A and copies it

Sheets("results").Select
Range("d2").Select
ActiveCell.Formula = "=address(match(1e+30,a:a),column(a1)) "
Range("$A$3:" & ActiveCell.Text).Select
Selection.Copy

Ivan

Sheets("results").Select Range("d2").Select ActiveCell.Formula = "=address(match(1e+30,a:a),column(a1)) " Range("$A$3"&":"&address(match(1e+30,a:a),column(a1))).Select Selection.Copy

Posted by Barry Ward on August 14, 2001 1:51 AM

That works fine, thanks again


:

Posted by Aladin Akyurek on August 14, 2001 2:46 AM

Ivan Thanks. Feel relieved. Would you also look at Phil's problem which also needs VBA.

Aladin

Sheets("results").Select Range("d2").Select ActiveCell.Formula = "=address(match(1e+30,a:a),column(a1)) " Range("$A$3"&":"&address(match(1e+30,a:a),column(a1))).Select Selection.Copy

Posted by Ivan F Moala on August 14, 2001 7:47 PM

Sure thing Aladin

Aladin Sheets("results").Select Range("d2").Select ActiveCell.Formula = "=address(match(1e+30,a:a),column(a1)) " Range("$A$3"&":"&address(match(1e+30,a:a),column(a1))).Select Selection.Copy