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

studentlearner

New Member
Joined
Oct 7, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi
Maybe some thing like

VBA Code:
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)"
 
Upvote 0
Another option
VBA Code:
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)"
 
Upvote 0
Solution
Hi
Maybe some thing like

VBA Code:
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
 
Upvote 0
Have you tried the code I supplied?
 
Upvote 0
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')))
 
Upvote 0
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:
Excel 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.
 
Upvote 0
I managed to remove the apostrophe by using the replace function, so all is good!! Thanks, guys!
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top