Setting Ranges

[TD Rich]

Hello,

I am trying to set a range that will go from cell A2 - the end of the data on the sheet.


Sheets("Approvals").Activate
Range("A2:Selection.End(xlDown").Copy

Can anybody let me know where i am going wrong please?

Thanks for your help.

Regards,

TD Rich.

 

[VoG]

Try

Sheets("Approvals").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).Copy

 

[Misca]

Here's a couple of different ways to select data. To copy the same data just change the ".select" to ".copy":

'This one selects the whole range from column A:
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
'It's from the bottom up so it doesn't stop on blanks

'This one selects the whole used range including other columns:
Range("A2", ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell)).Select
'It not only looks for values but any blank cells with some changes made on them
'(ie. font is bold or cell has color, comments or borders on it)

'The following code only selects the area with blank cells around it:
Dim Rng As Range

Set Rng = Range("A2").CurrentRegion
Rng.Offset(1, 0).Resize(Rng.Columns.Count, Rng.Rows.Count - 1).Select
'This one only selects the Range("A2").UsedRegion (without the possible row 1 data).
'I'm using Rng-variable to make it easier to read / understand
'Without the variable I'd have to replace all the Rng:s with the "Range("A2").CurrentRegion":
'Range("A2").CurrentRegion.Offset(1, 0).Resize(Range("A2").CurrentRegion.Columns.Count, Range("A2").CurrentRegion.Rows.Count - 1).Select