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

[senker]

This is the one that it seems how many forums or formulas I try I can't figure out!

I always have various ranges that, it could be 1 row down or it can be 100, how do I make the macro figure out far to go?

 

[njimack]

You could create dynamic ranges, or determine the last row and column using:

LastRow = Cells(Rows.Count,1).End(xlUp).Row
LastCol = Cells(1,Columns.Count).End(xlToLeft).Column

 

[agalick]

Neil's solution will work for finding the last row where column A is used in the workbook. If you want the exact behavior of Ctrl+Shift+Down, use xlDown instead of xlUp:

Dim cl As Range
Set cl = ActiveCell
Range(cl, cl.End(xlDown)).Select

Like Ctrl+Shift+Down, if you are on the last entry of a column it will take you all the way to the bottom of the worksheet, so watch out for that.

-Amy Galick

 

[senker]

thanks for the replys but still no luck:

I have various ranges, could be as short as 2 rows or as long as 50+

Code I have:

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

 

[agalick]

What are you trying to do with this Sub? Right now the line corresponding to Ctrl+Shift+Down is not really doing anything, because in the very next line you select something different.

Sub q()
'
' q Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveCell.Range("A1:AD1").Select 'expand the selection to the right so that it has 30 columns and 1 row
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select 'select more rows as if you had pushed ctrl+shift+down
    ActiveCell.Range("A1:AD12").Select 'select 30 columns and 12 rows
    ActiveSheet.Paste 'paste the 1x30 copied range into the 12x30 selected range -- repeating the data in each row
    Selection.End(xlDown).Select 'select the last (probably 12th) cell in the column where you started
End Sub