Replicate - 'Ctrl+Shift+DownArrow' in Macro

[Frank Kabel]

Hi
try
usedrange.select

 

[Skip]

Hello,

I'm trying to copy data from several worksheets into 1 worksheet.

I can do it manually by selecting Row 6 and then 'Ctrl+Shift+DownArrow' to
highlight all active rows, then copy and paste. However when I record this
on the Macro Recorder it just specifies the selected range which is no use
because this will vary sheet by sheet. I'm aware of
'Selection.End(xlDown).Select' which selects the last active cell, so having
selected Row 6, I tried 'Range.End(xlDown).Select' but this produces errors
(it was just a shot in the dark).

Can anyone help?

Paul.

 

[Jim Rech]

However when I record this on the Macro Recorder it just specifies the
When the Stop Recording toolbar appears click the second tool button to get
relative recording rather than absolute. This will help in getting the
result you want in your macro although often you still need to do some
editing.

--
Jim Rech
Excel MVP
| Hello,
|
| I'm trying to copy data from several worksheets into 1 worksheet.
|
| I can do it manually by selecting Row 6 and then 'Ctrl+Shift+DownArrow' to
| highlight all active rows, then copy and paste. However when I record this
| on the Macro Recorder it just specifies the selected range which is no use
| because this will vary sheet by sheet. I'm aware of
| 'Selection.End(xlDown).Select' which selects the last active cell, so
having
| selected Row 6, I tried 'Range.End(xlDown).Select' but this produces
errors
| (it was just a shot in the dark).
|
| Can anyone help?
|
| Paul.
|
|

 

[Skip]

Thanks Guys

My coding knowledge isn't great and I couldn't get Usedrange.Select to work.
I figured out a workaround which involves copying an excessive fixed range
from each sheet (5000 rows) and between each paste:

Getting a permanently blank column, selecting a cell
'Selection.End(xlDown).Select, (get to very last row of worksheet)
Selection.End(xlToLeft).Select, (get to very first column of worksheet)
Selection.End(xlUp).Select, (get to last row with data)
Selection.Offset(1, 0).Select (select row below).

Bit longwinded but it works and at least its automated.

Once again - many thanks.

 

[Bob Flanagan]

Try:

Range(Activecell, activecell.end(xldown)).entirerow.Select

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[P Daulton]

or:
Activesheet.Usedrange.entirerow.select
or
Activesheet.Usedrange.select
?

Pascal