Copying data down the column til then "nth" row

[nirvehex]

Hi,

I have data that is pulling from a table, Table2. In column A on a separate work sheet called "Client", starting in row 2, the formula is:

=OFFSET(Table2[@LocationCode],-1,0,2,1)

Is there a way I can make this auto populate down column A to the "nth" row so that it is the same amount of rows as the table is?

Otherwise I have to go count how many rows are in the table and then copy the formula down that many rows on the client worksheet.

Here's my code so far that is not quite working:

Sub PreBidClient()
Sheets("Client Pre-Bid").Select
Dim LR As Long
LR = .Formula = "=COUNTA(Table2[[#All],[LocationCode]])-1"
Range("A2:A" & LR).Formula = "=OFFSET(Table2[@LocationCode],-1,0,2,1)"
End Sub

Any ideas?

Thank you!

[Jacob Hilderbrand]

Hi

To find the end of a range or table based on a certain columns you can use this:

[VBA]
LastRow = Range("A" & Rows.Count).End(xlup).Row
[/VBA]

Then when you fill a formula you can use that variable.

Note that with a table if the cells are all blank in a column and you fill in the formula for the first row it will update to all rows automatically. If it is a standard range then you need to populate the formula in each cell.

[snb]

Don't forget: the table is an object and has properties:

Sub M_snb()
    y = Sheet1.ListObjects(1).Range.Rows.Count
End Sub