Creating a Macro to adjust in row number change

[Sathish KV]

Hi All,

Thought to share a useful tip!

Always wondered how to create a macro to copy paste formulas to last active cell which may differ on a regular basis

Well the best way of achieving this is to define the last active cell

e.g. If the column A has datas upto certain rows which may change often and you need a macro to create, copy and paste a formula in column B and adjust to the last active cell of column A, then defining the last active cell in column A may come in handy as mentioned below

Sub Macro1()
   
  lr = Range("A1048576").End(xlUp).Row
  Range("B2:B" & lr).FormulaR1C1 = "=RC[-1]+1"
 
End Sub

 

[shrivallabha]

One small suggestion:
Instead of hardcoding rows part:

lr = Range("A1048576").End(xlUp).Row

it can be written as

lr = Range("A" & Rows.Count).End(xlUp).Row

 

[Hui]

Sathish

Shrivallabha's suggestion is because it will allow code to work in Excel versions prior to 2007, which only permitted 65,536 rows without errors

 

[Sathish KV]

Thanks for the update Shrivallabha

Yes Hui you are absolutely right. Thanks!