Solved

Find area, select and copy

Posted on 2011-09-07
4
208 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 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 30

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

Independent Software Vendors: 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

751 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