CTRL + Shift + Down in a Macro?!!?

[senker]

Hi Everyone,

Is it possible to do CTRL + SHIFT + DOWN in a Macro?

I've been search all over the internet and videos and I can't find a straight answer for various number of rows.

VBA I have now:


Sub q()
'
' q Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Range("A1:AD1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:AD12").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub

[fb7894]

Range("A1").End(xlDown)

[mperrah]

Something like this:

Sub q()
Dim lr As Integer
' q Macro
' Keyboard Shortcut: Ctrl+q

' this counts down to last cell used in column AD - not fool proof but usually effective
lr = Cells(Rows.Count, "AD").End(xlUp).Row

' this copies first row A:AD
Range("A1:AD1").Select
Selection.Copy

' this pastes A1:AD1 down to last row (lr)
Range("A1:AD" & lr).Select
ActiveSheet.Paste

End Sub

[SamT]

Is it possible to do CTRL + SHIFT + DOWN in a Macro?

Range(X:Y).End(xlDown).Select

.End(xldown) is the VBA equivalent of CTRL+DOWN. Adding the .Select is the same as CTRL+SHIFT+DOWN
See also (xlUp), (xlToRight), and (xlToLeft).

A similar result is

Set A = Range(Range(X), Range(Y).End(xlDown))
The Variable A then represents the entire Range from Range(X) to the bottom used cell of the Column of Range(Y).