Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

macro to select range

Posted on 2012-08-23
4
Medium Priority
?
468 Views
Last Modified: 2012-08-23
Hi. I tried recording a macro with relative references turned on.
I want to select a range that could have a varying number of rows except for the last row. I got the code attached but it has the references B46 which would not work if there were a different number of row. Anyone know what change to make?
Thank you.
Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1:B46").Select
End Sub

Open in new window

0
Comment
Question by:willie108
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 38327603
Try this.
Range(Range("A1"), Range("A1").End(xlDown).End(xlToRight)).Select

Open in new window

Note, in Excel VBA you rarely, if ever, need to select something.
0
 

Author Comment

by:willie108
ID: 38327614
Thanks. I need to not select the last row. I want to select the whole range except the last row of the range.
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38327736
Try this.
Range(Range("A1"), Range("A1").End(xlDown).End(xlToRight).Offset(-1)).Select 

Open in new window

0
 

Author Closing Comment

by:willie108
ID: 38327820
Thanks
0

Featured Post

Technology Partners: 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

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

810 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