• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a Macro to adjust in row number change

Sathish KV

Active Member
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

Code:
Sub Macro1()
   
  lr = Range("A1048576").End(xlUp).Row
  Range("B2:B" & lr).FormulaR1C1 = "=RC[-1]+1"
 
End Sub
 
Last edited:
One small suggestion:
Instead of hardcoding rows part:
Code:
lr = Range("A1048576").End(xlUp).Row
it can be written as
Code:
lr = Range("A" & Rows.Count).End(xlUp).Row
 
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
 
Back
Top