How to find the last row/cell with data and use it as a variable to input into an argument as a range for an Excel formula in VBA?

Asked Mar 23 '22 at 20:39

Active Mar 23 '22 at 20:39

Viewed 12 times

I want to use VBA code to find the last row of data in column B then take that Range and utilize it in a formula for another cell.

Here's an example: Worksheets("Learning").Range("F9").Formula = "=T.TEST(B3:B17,C3:C17,2,1)"

I want to make those ranges "(B3:B17, C3:C17)" dynamic, so as the table is updated I don't have to update the code.

asked Mar 23 '22 at 20:39

garry mullins

`With Worksheets("Learning")`, `Dim lastRow As Long`, `lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row`, `End With`. – BigBen Mar 23 '22 at 20:42
Or you could use a formula like `=T.TEST(B3:INDEX(B:B,MATCH(1E+99,B:B,1)),C3:INDEX(C:C,MATCH(1E+99,C:C,1)),2,1)`. – BigBen Mar 23 '22 at 20:45

0 Answers0