How to select the the last cell of the table in vba

[studentlearner]

Hi Guys, so i recorded a formula using macros, currently the macro will be within a fixed range, but i require it to find the last cell inside the table. Question is How do modify it so that it'll find the last cell of the table?

this is the formula i'm working with:
=SORTBY(SEQUENCE(ROWS(L2:L37)),RANDARRAY(ROWS(L2:L37)))
with
=INDEX(L2:L37,M2#,1)

and this the recorded macro when i apply the formula that require modification:
Range("M2").Select
ActiveCell.Formula2R1C1 = _
"=SORTBY(SEQUENCE(ROWS(RC[-1]:R[37]C[-1])),RANDARRAY(ROWS(RC[-1]:R[37]C[-1])))"
Range("N2").Select
ActiveCell.Formula2R1C1 = "=INDEX(RC[-2]:R[37]C[-2],RC[-1]#,1)"

As you can see from the recorded macro it'll only stop at Row 37 but the function i'm working with will be constantly updating and so i'm not sure how do i integrate 'End(xlDown)' or 'LR' onto the formula the macro recorded. or some other stuff.

Any help would be great thanks!!

 

[alansidman]

VBA Tutorial: Find the Last Row, Column, or Cell in Excel

Learn 3 VBA methods to find the last row, column, or cell in a worksheet. The layout of your data and blank cells will determine which method to use.

www.excelcampus.com

 

[mohadin]

Hi
Maybe some thing like

LR = Cells(Rows.Count, "L").End(xlUp).Row

Range("M2").Select
ActiveCell.FormulaR1C1 = "=SORTBY(SEQUENCE(ROWS(RC[-1]:R[" & LR & "]C[-1])),RANDARRAY(ROWS(RC[-1]:R[" & LR & "]C[-1])))"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=INDEX(RC[-2]:R[" & LR & "]C[-2],RC[-1],1)"

 

[Fluff]

Another option

Dim lr
lr = Cells(Rows.Count, "L").End(xlUp).Row

Range("M2").Formula2 = "=SORTBY(SEQUENCE(ROWS(L2:L" & lr & ")),RANDARRAY(ROWS(L2:L" & lr & ")))"
Range("N2").Formula2 = "=INDEX(L2:L" & lr & ",M2#,1)"

 

[studentlearner]

Hi
Maybe some thing like

LR = Cells(Rows.Count, "L").End(xlUp).Row

Range("M2").Select
ActiveCell.FormulaR1C1 = "=SORTBY(SEQUENCE(ROWS(RC[-1]:R[" & LR & "]C[-1])),RANDARRAY(ROWS(RC[-1]:R[" & LR & "]C[-1])))"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=INDEX(RC[-2]:R[" & LR & "]C[-2],RC[-1],1)"

this work nicely, however, it kind of somehow overcounts an extra two rows, which I find very weird

 

[Fluff]

Have you tried the code I supplied?

 

[studentlearner]

Have you tried the code I supplied?

Hey, I tried yours too, but after some troubleshooting, I realize there's an ' on the range of formula. is there a way to overcome this obstacle?

Heres the VBA:
Dim LR
LR = Cells(Rows.Count, "L").End(xlUp).Row


Range("M2").Select
ActiveCell.Formula2R1C1 = _
"=SORTBY(L2:L" & LR & ",RANDARRAY(ROWS(L2:L" & LR & ")))"

and here's the output in the formula bar, notice there's a ' between the range:
=SORTBY('L2':'L46',RANDARRAY(ROWS('L2':'L46')))

 

[Akuini]

but i require it to find the last cell inside the table.

Is this an actual table (not just range)?
If it's an actual table, say Table1, then you can get last table row with this formula:

=MIN(ROW(Table1))+ROWS(Table1)-1

you can use it in your formula, so you don't need a macro to adjust it when Table data changes.

 

[studentlearner]

I managed to remove the apostrophe by using the replace function, so all is good!! Thanks, guys!

 

[Fluff]

Glad you sorted it & thanks for the feedback.