Solved

Find area, select and copy

Posted on 2011-09-07
4
207 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Seamus2626
4 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 36494592
Assuming you always have data, I think this should work:

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

Author Closing Comment

by:Seamus2626
ID: 36494616
Spot on, cheers Stephen

Seamus
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36494634
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36494655
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
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question