Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Finding a relative range.

Posted on 2014-09-12
11
Medium Priority
?
95 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 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