Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Script
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Script
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Script
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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Script help | Excel Worksheet Functions | |||
VB script/macro help - please !! | Excel Discussion (Misc queries) | |||
Macro or script | Excel Programming | |||
Macro or Script??? | Excel Programming |