[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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

0
RWayneH
Asked:
RWayneH
  • 6
  • 2
  • 2
  • +1
1 Solution
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
RWayneHAuthor Commented:
This worked!!  I replaced this with the xlDown line.  Thanks for the help!!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now