Find area, select and copy

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi,

I need a piece of code that can select the area of the attached spreadsheet.

I canT use cells select as i can copy exactly the area ("A1:W52")

This area will change each day though so i need the code to loop through A, find the first blank cell, then move across to W on the same row and back up to the top

Ao it will always be Columne "A:W" but the rows will vary

Thanks
Seamus


test.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Assuming you always have data, I think this should work:

Range("A1", Range("A1").End(xlDown)).Resize(, 23).Select

Author

Commented:
Spot on, cheers Stephen

Seamus
Rob HensonFinance Analyst

Commented:
Does selecting Current Region work for you?

Before coding it, select A1 and then press "Ctrl + *" or "Ctrl + Shift + 8" (keypad 8 as opposed to number pad 8, shift + 8 being *).

Does this select the right area?

For code this would be:

Range("A1").Select
CurrentRegion.Select

Open in new window


Alternatiely create a dynamic Named Range using OFFSET to define the area then the code can select the named area.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),23)

Thanks
Rob H
for you in the attached sheet what is the area that need to be selected ? A1:W52 ? if yes then try the following file
Press on the command button Select A:W and check the results. You need to enable macros
gowflow
test.xls

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial