Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA to select range of cells from A2 to ........

Posted on 2013-12-10
10
Medium Priority
?
403 Views
Last Modified: 2013-12-10
Can an Expert provide me with the VBA code I need to select a range of cells from A2 but where the end cell could change so it could be AM500 or AT1000 etc etc

Thanks
0
Comment
Question by:Jagwarman
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39708549
a row based selection might look like this:
Range(Selection, Selection.End(xlToRight)).Select

Open in new window

whereas "Selection" could be "Range("A2")"
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39708565
Range(Range("A2"), Cells.Find("*", Range("A1"), , , , xlPrevious)).Select
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39708790
Assuming continuous data in at least one row and one column:

Range("A2").Select
CurrentRegion.Select


CurrentRegion.Select is equivalent to keyboard selection of "Ctrl + *"

Thanks
Rob H
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Jagwarman
ID: 39708875
Rob H

Unfortunately there are some columns with nothing in them so this does not work on my current file.
0
 

Author Comment

by:Jagwarman
ID: 39708883
ssaqibh

this a,most works but for some reason it goes to AK but there is data in AL which it is not capturing ???
0
 

Author Comment

by:Jagwarman
ID: 39708952
Alex140181 this does not work for me because there are blank rows in between. but thanks for trying
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39709075
Do the empty columns have a header? If they do then it should still work as the header row is constant. If not, hence why the EndRight suggestion doesn't work.

Would inserting just a # or just a space in the relevant columns be a workaround; just to populate these columns so that the columns beyond these get included in current region?

If populated the EndRight would also then work.

Thanks
Rob H
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39709082
Then try

Range(Range("A2"), Cells.SpecialCells(xlCellTypeLastCell)).Select
0
 

Author Comment

by:Jagwarman
ID: 39709105
Rob H

I do use that in other circumstances but it won't work in this one but thanks for trying.
0
 

Author Closing Comment

by:Jagwarman
ID: 39709109
Exactly what I was looking for. Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

580 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