So I've read a ton of posts on this but none answer my specific question.
I am creating a macro and want to use the Ctrl+Shift+Down within a formula so the the formula is applied to multiple ranges of varied size.
Here is my macro.
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+i
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=Delimit(R[1]C:R[21]C)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(21, 0).Range("A1").Select
End Sub
Within the second line of the macro the function is referencing "R[1]C:R[21]C" . On the third to last line i see "Range(Selection, Selection.End(xlDown)).Select" which refers to ctr+shift+down. How would i get the fuction in my macro to select a range using that method instead of the "R[1]C:R[21]C"?
I tried pasting the "Range(selection..." into the Delimit() function but that does not seem to work.
If anyone can help I'd appreciate your input. Thank you.
I am creating a macro and want to use the Ctrl+Shift+Down within a formula so the the formula is applied to multiple ranges of varied size.
Here is my macro.
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+i
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=Delimit(R[1]C:R[21]C)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(21, 0).Range("A1").Select
End Sub
Within the second line of the macro the function is referencing "R[1]C:R[21]C" . On the third to last line i see "Range(Selection, Selection.End(xlDown)).Select" which refers to ctr+shift+down. How would i get the fuction in my macro to select a range using that method instead of the "R[1]C:R[21]C"?
I tried pasting the "Range(selection..." into the Delimit() function but that does not seem to work.
If anyone can help I'd appreciate your input. Thank you.