Finding a relative range.

Ln4.  I had "Use Relative Reference" on when recording this, but Ln4 does not look relative.
How would I tell it to goto the bottom then move 5 rows up from there?  It will not be 995 rows each time.

    If ActiveCell = "" Then
        ActiveCell.Offset(0, -1).Range("A1").Select  'move one cell left from whereever active cell is
        Range(Selection, Selection.End(xlDown)).Select
        ActiveCell.Range("A1:C995").Select
        Selection.Copy
        Windows("CU50BySalesOrder.xlsm").Activate
        Sheets("MasterCopy").Select
'elminate the need to start marco on specific cell
        Range("D2").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 1).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Select
    End If

Open in new window

RWayneHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

ActiveCell.Range(Range("A1"), Range("C" & Rows.Count).End(xlUp).Offset(-5).Select

Regards
0
RWayneHAuthor Commented:
Should it be red? when pasting this into the editor?
0
Rgonzo1971Commented:
No pls try

ActiveCell.Range(Range("A1"), Range("C" & Rows.Count).End(xlUp).Offset(-5)).Select
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

RWayneHAuthor Commented:
Problem.... I need it to go xlDown then up the -5.  For some reason when it uses xlUp it is finding something before it gets to the needed cell.  Not sure what it sees.  Is there a way to honor my Ln2?  I need to bring along the two columns to the right with it and somehow move it up -5 from that point .
0
RWayneHAuthor Commented:
I have the whole area selected when I run the following
    Range("A1").Select
        ActiveCell.Offset(34, 4).Range("A1:C1").Select
        Range(Selection, Selection.End(xlDown)).Select

Open in new window


I just do not need the last 5 row of this selected range.  How do I tell it to -5 off the bottom?
0
Glenn RayExcel VBA DeveloperCommented:
If you want it to select all but the last five rows of the contiguous range defined in columns A:C and then change Line 4 from
ActiveCell.Range("A1:C995").Select
to
Range("A1:C" & Range("A1").End(xlDown).Row - 5).Select

I removed "ActiveCell." because including it moves the selected range to a relative range based on the selection in A1:Cnnn.  I'm not sure how you're using that in this case, but you can add it back if you need.

Regards,
-Glenn
0
RWayneHAuthor Commented:
This suggestion is failing?  Error:  Method 'Range' of object '_Global' failed.
0
Glenn RayExcel VBA DeveloperCommented:
I have tried several mockups with this code and the only time I get an error is if there is:
1) no data in column A
2) less than five rows of contiguous data from cell A1 (ex. A1:A4)

Sample data sheets are always helpful.

-Glenn
0
RWayneHAuthor Commented:
Active cell starts on cell A1:
Range("A1").Select

next it selects cell E35 thru G35
        ActiveCell.Offset(34, 4).Range("A1:C1").Select

selects from there to the bottom of the dataset.
        Range(Selection, Selection.End(xlDown)).Select

I need to know how to not include the bottom 5 rows... as part of this
SampleFile.xls
0
ThomasMcA2Commented:
You only need to add the Offset() function, like this:

Range(Selection, Selection.End(xlDown).Offset(-5)).Select
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
This worked!!  I replaced this with the xlDown line.  Thanks for the help!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.