Solved

Finding a relative range.

Posted on 2014-09-12
11
94 Views
Last Modified: 2014-09-15
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

0
Comment
Question by:RWayneH
[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
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40319917
Hi,

pls try

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

Regards
0
 

Author Comment

by:RWayneH
ID: 40319925
Should it be red? when pasting this into the editor?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40319931
No pls try

ActiveCell.Range(Range("A1"), Range("C" & Rows.Count).End(xlUp).Offset(-5)).Select
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:RWayneH
ID: 40320016
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
 

Author Comment

by:RWayneH
ID: 40320074
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40320076
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
 

Author Comment

by:RWayneH
ID: 40320791
This suggestion is failing?  Error:  Method 'Range' of object '_Global' failed.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40320948
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
 

Author Comment

by:RWayneH
ID: 40321317
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
 
LVL 14

Accepted Solution

by:
ThomasMcA2 earned 500 total points
ID: 40321374
You only need to add the Offset() function, like this:

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

Author Closing Comment

by:RWayneH
ID: 40323067
This worked!!  I replaced this with the xlDown line.  Thanks for the help!!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…

617 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