macro to select range

Posted on 2012-08-23
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(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
End Sub

Open in new window

Question by:willie108
    LVL 33

    Expert Comment

    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.

    Author Comment

    Thanks. I need to not select the last row. I want to select the whole range except the last row of the range.
    LVL 33

    Accepted Solution

    Try this.
    Range(Range("A1"), Range("A1").End(xlDown).End(xlToRight).Offset(-1)).Select 

    Open in new window


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now