Macro Script

S

Shams

Folks,
I have the following Macro Script generated by recording Excel steps:

Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"" ""&RC[4]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D14")
Range("D2:D14").Select

This is basically concatenating the content of 2 other cells in D2 and then
copying down the formula....
as you can see when I am recording the copy down step by doubleclicking into
the corner of cell D2 it is copying until D14 which happens to be the end of
the current data range....

Now, with this structure it will always stop at D14 regardless of the number
of rows...is there anyway I can add a syntax so that the macro will
automatically evaluate to the last row of Column D to autoFill the formula?

Thanks in advance for your help.

Regards,
Shams.
 
J

Joel

I think you want the last row of Column C since there is no data in column D
until you put in the formula

LastRow = Range("C" & Rows.Count).end(xlup).Row
Range("D2").FormulaR1C1 = "=RC[-1]&"" ""&RC[4]"
Range("D2").AutoFill Destination:=Range("D2:D" & LastRow)
 
S

Shams

Joel,
Thank you for your fast input. Yes! This seems to be working flawlessly!!
I basically replaced my syntax with what you provided me and it makes good
sense.

Joel said:
I think you want the last row of Column C since there is no data in column D
until you put in the formula

LastRow = Range("C" & Rows.Count).end(xlup).Row
Range("D2").FormulaR1C1 = "=RC[-1]&"" ""&RC[4]"
Range("D2").AutoFill Destination:=Range("D2:D" & LastRow)


Shams said:
Folks,
I have the following Macro Script generated by recording Excel steps:

Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"" ""&RC[4]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D14")
Range("D2:D14").Select

This is basically concatenating the content of 2 other cells in D2 and then
copying down the formula....
as you can see when I am recording the copy down step by doubleclicking into
the corner of cell D2 it is copying until D14 which happens to be the end of
the current data range....

Now, with this structure it will always stop at D14 regardless of the number
of rows...is there anyway I can add a syntax so that the macro will
automatically evaluate to the last row of Column D to autoFill the formula?

Thanks in advance for your help.

Regards,
Shams.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top