How to fill a variable number of cells 1

[henio]

Hi all,

I recorded a macro for a simple task. I create a formula to format a cell based on the values of three others. I then copy it down to the end of the range.

The recorder gave me:

Sub Macro2()

'
Columns("D&quot.Select
Selection.Insert Shift:=xlToRight
Range("D2&quot.Select
ActiveCell.FormulaR1C1 = _
"=TEXT(RC[-3],""0000"&quot&TEXT(RC[-2],""0000"&quot&TEXT(RC[-1],""0000"&quot"
Range("D2&quot.Select
Selection.AutoFill Destination:=Range("D25&quot
End Sub

However, there may be any number of rows - the range in the recorded macro is fixed. How can I modify this to allow for a variable number of rows?

Thanks,
Henio

 

[SkipVought]

What is the D Destination Range based on? What will tell me that it should start in row 2 and end in row 5?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi henio,

Assuming that you want to fill column D down from d2 as far as you have data in column A, you can do this ..

Range([A2],[A2].End(xldown)).offset(0,3).FormulaR1C1 =  "=TEXT(RC[-3],""0000"")&TEXT(RC[-2],""0000"")&TEXT(RC[-1],""0000"")"

Enjoy,
Tony

 

[henio]

Tony,

thanks for this. Sorry if my question was not clear enough, but you were spot on - definitely star quality!

Henio