Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find area, select and copy

Posted on 2011-09-07
4
Medium Priority
?
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 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 31

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

715 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