Range("AR65536").End(xlUp).Offset(-7).Select question

[redspanna]

Hi all

here is what I'm trying to achieve...

Sheet "sheet1"
Find last populated cell in column AR
move up 7 rows
copy AR:AX 7 rows of data
Find last populated cell in column AR..down 1 (ie blank cell)
paste copied AR:AX cells

example...

AR100 is last populated cell
so move upto AR93
copy AR93:AX100
move back to AR100
Down one row
paste AR93:AX100

I'm getting stuck with the X1up and ranges to copy etc so any help appreciated
thanks

 

[VoG]

Try

Sheets("Sheet1").Range("AR" & Rows.Count).End(xlUp).Offset(-6).Resize(7, 6).Copy Destination:=Sheets("Sheet1").Range("AR" & Rows.Count).End(xlUp).Offset(1)

 

[Norie]

To get the results you indicate try this.

Dim rngEnd As Range
 
Set rngEnd = Worksheets("Sheet1").Cells(Rows.Count, "AR").End(xlUp)
 
rngEnd.Offset(-7).Resize(8, 7).Copy rngEnd.Offset(1)

You'll notice though that it doesn't quite match your initial directions.

Probably just me getting mixed up though.

 

[JamesW]

Hi there,

Try this:

Sub test()
    Dim lRow, lRowOffset, blankRow As Double
    
    lRow = Range("AR" & Application.Rows.Count).End(xlUp).Row
    lRowOffset = lRow - 7
    blankRow = lRow + 1
    Range("AR" & lRowOffset & ":AX" & lRowOffset).Copy Destination:=Range("AR" & blankRow)
End Sub

 

[hiker95]

redspanna,


Sample data before the macro code:

Excel Workbook
	AR	AS	AT	AU	AV	AW	AX
92	92	92	92	92	92	92	92
93	93	93	93	93	93	93	93
94	94	94	94	94	94	94	94
95	95	95	95	95	95	95	95
96	96	96	96	96	96	96	96
97	97	97	97	97	97	97	97
98	98	98	98	98	98	98	98
99	99	99	99	99	99	99	99
100	100	100	100	100	100	100	100
101
102
103
104
105
106
107
108
109
Sheet1

After the macro code:

Excel Workbook
	AR	AS	AT	AU	AV	AW	AX
92	92	92	92	92	92	92	92
93	93	93	93	93	93	93	93
94	94	94	94	94	94	94	94
95	95	95	95	95	95	95	95
96	96	96	96	96	96	96	96
97	97	97	97	97	97	97	97
98	98	98	98	98	98	98	98
99	99	99	99	99	99	99	99
100	100	100	100	100	100	100	100
101	93	93	93	93	93	93	93
102	94	94	94	94	94	94	94
103	95	95	95	95	95	95	95
104	96	96	96	96	96	96	96
105	97	97	97	97	97	97	97
106	98	98	98	98	98	98	98
107	99	99	99	99	99	99	99
108	100	100	100	100	100	100	100
109
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sheets("Sheet1").Range("AR" & Rows.Count).End(xlUp).Offset(-7).Resize(8, 7).Copy Destination:=Sheets("Sheet1").Range("AR" & Rows.Count).End(xlUp).Offset(1)

 

[redspanna]

Guys,

thank you all so much really appreciate your help