Adding Ctr+Shift+Down to a fuction within a macro

[neknim]

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.

 

[Weaver]

what you need to do is use range.end(xldown)

Although I think in order to help you further, I need you to describe what you're trying to achieve more fully.

 

[neknim]

What I'm trying to do is insert a function into a number of cells via a macro. The CTRL+SHIFT+DOWN will select the proper range to include in a single function because the column has blank cells in them that differentiate each range to be selected. For example

Name............ID............Network............Status
Smith............001.........[=Delim()]...........[=Delim()]
..................................green...................Y
...................................blue...................N
...................................red.....................Y
..................................orange.................N
Roberts..........002.........[=Delim()]............[=Delim()]
....................................yellow................Y
.....................................red...................Y
....................................green.................N
....................................blue...................Y


The first two spots under netwrok and status are the cells in which i will add my function. The function is meant to delimit the info under network and status. The macro I'm trying to create will start under network, select only until the next blank cell, and then drag the function across so that i have the function delimiting the info under both network and status. Does that make sense?

Thank you for the info. I did try what you said above but inserting that piece did not produce a value in the function. If you have any other ideas I'd appreciate it. Thank you again.