Offset variable range

[Gene Augustin]

I macrod a one column range of cells named "Item" and did a series fill to
number the rows, about 300 and varies from month to month.

In the following I'm trying to copy the values of Item, Move the range down
like Option-Return, paste the values,
Option-Return and paste the values again.
I'm trying to get a range three times as long as the original range like
1 2 3 4 1 2 3 4 1 2 3 4
So I can sort and get 1 1 1 2 2 2 3 3 3 4 4 4

Can't get there with the macro recorder.



Application.Goto Reference:="Item"
Selection.Copy
Range.Offset ("Item") 'This is the problem. Need a number
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range.Offset ("Item")
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[Bob Greenblatt]

I macrod a one column range of cells named "Item" and did a series fill to
number the rows, about 300 and varies from month to month.

In the following I'm trying to copy the values of Item, Move the range down
like Option-Return, paste the values,
Option-Return and paste the values again.
I'm trying to get a range three times as long as the original range like
1 2 3 4 1 2 3 4 1 2 3 4
So I can sort and get 1 1 1 2 2 2 3 3 3 4 4 4

Can't get there with the macro recorder.



Application.Goto Reference:="Item"
Selection.Copy
Range.Offset ("Item") 'This is the problem. Need a number
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range.Offset ("Item")
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Try:
Range.offset(rows("item"))

 

[Shane Devenshire]

Hi,

I've modified your code to:

n = [Item].Count
[Item].Copy
[Item].Offset(n).Resize(2 * n).PasteSpecial Paste:=xlValues

This replaces all of your code.

 

[Gene Augustin]

Tremendous help Shane. Where is the YES button?