VBA Help. Drag colum to the last row with data

[HeyYouYesYouNoNotYouYou]

Hi,

I import data from a raw cvs file and run formatting macros. However i want a unique number on each row in the A colum which will be used as a primary key in access. I have made the formula to generate a unique number, but i need a macro to drag down the formula to the last populated row everytime it is updated. Could anybody suggest VBA code that will allow me to do this.

Thanks in Advance,

HeyYouYesYouNoNotYouYou

• 
  

 

[njimack]

Sub test()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(2, 1), Cells(LastRow, 1)).Formula = "Your formula goes here"
End Sub

 

[Teeroy]

Welcome to MrExcel.

One way to do this is, assuming your formula is in StartCell is:

Sub dragFormula()
Set StartCell = Range("A2")
StartCell.Resize(ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Rows.Count, 1).Row - StartCell.Row + 1, 1).FillDown
End Sub

 

[HeyYouYesYouNoNotYouYou]

Thanks to both of you. They both worked perfectly.

Cheers.