Best Solution / Ranges

[Guest]

I need to first thank all those who have been very helpful with my questions. They have saved me hours of frustrations and searching

Ok..

I have a range of data and I need to add several rows of data to the end of the range. How do I extend the range to cover the new data? I don't want to do it row by row, I would like to add all the rows and then extend the range

The range on sheets(2) is from A to F

I was thinking along the lines of acquiring the starting cell of the old range and the ending cell of the new data. If I am right, how do I do this? Is there a better way? I would like to know

Thanks

 

[Tom Ogilvy]

Dim rng as Range
With worksheets(2)
set rng = .cells(rows.count,1).End(xlup).Offset(1,0)
End With

worksheets(1).Range("A1").CurrentRegion.Copy _
Destination:=rng

' if you mean named range, now to redefine

rng.CurrentRegion.Resize(,6).Name = "MyData"

--
Regards,
Tom Ogilvy

I need to first thank all those who have been very helpful with my

questions. They have saved me hours of frustrations and searching.

Ok...

I have a range of data and I need to add several rows of data to the end

of the range. How do I extend the range to cover the new data? I don't want
to do it row by row, I would like to add all the rows and then extend the
range.

The range on sheets(2) is from A to F.

I was thinking along the lines of acquiring the starting cell of the old

range and the ending cell of the new data. If I am right, how do I do this?
Is there a better way? I would like to know.

 

[Otto Moehrbach]

Troy
One way:
Say old range is A1:F12 and is named "TheRng"
Say you want to name new range, say A1:F15, "TheRng"
Say you know that Column A goes all the way down
Sub ExtendRng()
Range(Range("TheRng")(1), Range("A" & Rows.Count). _
End(xlUp).Offset(, 5)).Name = "TheRng"
MsgBox Range("TheRng").Address
End Sub
HTH Otto

I need to first thank all those who have been very helpful with my

questions. They have saved me hours of frustrations and searching.

Ok...

I have a range of data and I need to add several rows of data to the end

of the range. How do I extend the range to cover the new data? I don't want
to do it row by row, I would like to add all the rows and then extend the
range.

The range on sheets(2) is from A to F.

I was thinking along the lines of acquiring the starting cell of the old

range and the ending cell of the new data. If I am right, how do I do this?
Is there a better way? I would like to know.

 

[Guest]

The Graveyard shift must be effecting my thinking. I forgot to relay all the important data.

On sheet(1), the columns being used are C, G, & M, and the data needs to be copied into Columns A, B, & C on sheet(2).

The data will be copied at the end of an exising range. This range will need to be extended to cover the new data.

Does this make sense? I am so sleepy right now that I do not know if I am getting all the info out.

Thank for your help.

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range
With worksheets(2)
set rng = .cells(rows.count,1).End(xlup).Offset(1,0)
End With

With worksheets(1).
set rng1 = .Range(.cells(1,3),.cells(rows.count,3).End(xlup))
End with

rng1.copy Destination:=rng
rng1.offset(0,4).copy Destination:=rng.offset(0,1)
rng1.offset(0,10).copy Destination:=rng.offset(0,2)


--
Regards,
Tom Ogilvy

The Graveyard shift must be effecting my thinking. I forgot to relay all the important data.

On sheet(1), the columns being used are C, G, & M, and the data needs to

be copied into Columns A, B, & C on sheet(2).

The data will be copied at the end of an exising range. This range will

need to be extended to cover the new data.

 

[Guest]

This works great. It is better than I had hoped. It will take some time to figure out how it all works

One additional question though, after some trial and error, how do I set this up to copy only the value of the cells? My attempts at .PastSpecial Paste:=xlvalue has little to be desired.

 

[Tom Ogilvy]

rng1.copy Destination:=rng
rng1.offset(0,4).copy Destination:=rng.offset(0,1)
rng1.offset(0,10).copy Destination:=rng.offset(0,2)


becomes

rng1.copy
rng.pasteSpecial paste:=xlValues ' not xlvalues with an "s" on the end
rng1.offset(0,4).copy
rng.offset(0,1).pastespecial paste:=xlValues
rng1.offset(0,10).copy
rng.offset(0,2).pastespecial paste:=xlValues

--
Regards,
Tom Ogilvy

This works great. It is better than I had hoped. It will take some time to figure out how it all works.

One additional question though, after some trial and error, how do I set

this up to copy only the value of the cells? My attempts at .PastSpecial
Paste:=xlvalue has little to be desired.