• 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:

shrivallabha

Excel Ninja
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
 

Hui

Excel Ninja
Staff member
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
 
Top