RWayneH
asked on
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.
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
ASKER
Should it be red? when pasting this into the editor?
No pls try
ActiveCell.Range(Range("A1 "), Range("C" & Rows.Count).End(xlUp).Offs et(-5)).Se lect
ActiveCell.Range(Range("A1
ASKER
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 .
ASKER
I have the whole area selected when I run the following
I just do not need the last 5 row of this selected range. How do I tell it to -5 off the bottom?
Range("A1").Select
ActiveCell.Offset(34, 4).Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
I just do not need the last 5 row of this selected range. How do I tell it to -5 off the bottom?
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).Ro w - 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
ActiveCell.Range("A1:C995"
to
Range("A1:C" & Range("A1").End(xlDown).Ro
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
ASKER
This suggestion is failing? Error: Method 'Range' of object '_Global' failed.
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
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
ASKER
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)).Sel ect
I need to know how to not include the bottom 5 rows... as part of this
SampleFile.xls
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)).Sel
I need to know how to not include the bottom 5 rows... as part of this
SampleFile.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked!! I replaced this with the xlDown line. Thanks for the help!!
pls try
ActiveCell.Range(Range("A1
Regards