If the last row of all three columns is the same, then this common little snippet does just fine. (or if B has the greater last row)


Sub HMMMM()

Sheets("Sheet7").Range("B2:D10").Copy Sheets("Sheet8").Range("B" & Rows.Count).End(xlUp)(2)

End Sub


I want that capability along with should column D have a last row greater than B or C then paste to column B using columns D's last row.

So the code can past "uneven columns lenghts" always to B and to the last row of any, should they not be the same.

Thanks,
Howard

On Sunday, November 16, 2014 9:22:58 PM UTC-8, L. Howard wrote:
If the last row of all three columns is the same, then this common little snippet does just fine. (or if B has the greater last row)


Sub HMMMM()

Sheets("Sheet7").Range("B2:D10").Copy Sheets("Sheet8").Range("B" & Rows.Count).End(xlUp)(2)

End Sub


I want that capability along with should column D have a last row greater than B or C then paste to column B using columns D's last row.

So the code can past "uneven columns lenghts" always to B and to the last row of any, should they not be the same.

Thanks,
Howard

Edit:

Maybe the line should read:

So the code can paste "uneven columns lenghts" always to B and to the greater last row of any, should they not be the same.

Howard

If the last row of all three columns is the same, then this common
little snippet does just fine. (or if B has the greater last row)


Sub HMMMM()

Sheets("Sheet7").Range("B2:D10").Copy Sheets("Sheet8").Range("B" &
Rows.Count).End(xlUp)(2)

End Sub


I want that capability along with should column D have a last row
greater than B or C then paste to column B using columns D's last
row.

So the code can past "uneven columns lenghts" always to B and to the
last row of any, should they not be the same.

Thanks,
Howard

Why not?...

UsedRange.Rows.Count + 1

...which may or may not be the last row containing data, but will always
be 1 row below any rows that 'did' contain data.

IMO, what would be nice is if sheets had a LastDataRow (or LastDataCol)
property as does my Spread.ocx!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Why not?...

UsedRange.Rows.Count + 1

..which may or may not be the last row containing data, but will always
be 1 row below any rows that 'did' contain data.

IMO, what would be nice is if sheets had a LastDataRow (or LastDataCol)
property as does my Spread.ocx!!

--
Garry

That works like you say, I think, which gives inconsistent results. Along with copying a few blank rows in the sheet7 B2:D10 range it sorta is all goofed up.

Seems to respond best if column D is the longer copied and pasted column.

Not worth chasing, since it was just a curious query with no pending outcome waiting.

Thanks for taking a look.
Howard

Why not?...

UsedRange.Rows.Count + 1

..which may or may not be the last row containing data, but will
always be 1 row below any rows that 'did' contain data.

IMO, what would be nice is if sheets had a LastDataRow (or
LastDataCol) property as does my Spread.ocx!!

--
Garry

That works like you say, I think, which gives inconsistent results.
Along with copying a few blank rows in the sheet7 B2:D10 range it
sorta is all goofed up.

Seems to respond best if column D is the longer copied and pasted
column.

Not worth chasing, since it was just a curious query with no pending
outcome waiting.

Thanks for taking a look.
Howard

The blank rows are those that used to have data in them and so are
included in UsedRange! (keyword is 'Used')

What to do is to write yourself a function that returns the last row of
a range that contains data. Require a string arg containing the Address
of the range. Then you'll have your own "Get_LastDataRow" function that
you can 'drop' into any project<g...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Sun, 16 Nov 2014 23:13:39 -0800 (PST) schrieb L. Howard:

Seems to respond best if column D is the longer copied and pasted column.

try:

Sub Test()
Dim varLen(2) As Variant
Dim i As Long, n As Long
Dim FERow As Long

With Sheets("Sheet8")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next

FERow = WorksheetFunction.Max(varLen) + 1
Sheets("Sheet7").Range("B2:D10").Copy .Range("B" & FERow)
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

So if this was a function that accepts a range address...

Sheets("Sheet7").Range("B2:D10").Copy _
.Range("B" & Get_LastDataRow("B:D"))

...where the function returns only the max value!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, November 16, 2014 11:29:12 PM UTC-8, Claus Busch wrote:
Hi Howard,

Am Sun, 16 Nov 2014 23:13:39 -0800 (PST) schrieb L. Howard:

Seems to respond best if column D is the longer copied and pasted column.

try:

Sub Test()
Dim varLen(2) As Variant
Dim i As Long, n As Long
Dim FERow As Long

With Sheets("Sheet8")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next

FERow = WorksheetFunction.Max(varLen) + 1
Sheets("Sheet7").Range("B2:D10").Copy .Range("B" & FERow)
End With

End Sub


Regards
Claus B.
--

Hi Claus,

Wow! That really seems to work as far as I tested it.

No fair using magic! You are supposed to use VBA.<G

So if the FERow works for the sheet8 range, I tried to use a BERow for the range on sheet7.

The MSGBOX returns the first blank row past any data in B, C or D columns on sheet7, but then errors out with a subscript out of range.

Will this work if the subscript error is corrected? I can't see what to change.

Howard

Sub TestEXP()
Dim varLen(2) As Variant
Dim i As Long, n As Long
Dim FERow As Long, BERow As Long

With Sheets("Sheet7")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
BERow = WorksheetFunction.Max(varLen) + 1
MsgBox BERow
End With

With Sheets("Sheet8")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
FERow = WorksheetFunction.Max(varLen) + 1



Sheets("Sheet7").Range("B2:D" & BERow).Copy .Range("B" & FERow)
End With

End Sub

This seems to work, where I tested with each column being the longest on sheet 7 and it copies to sheet 8 as wanted.

Howard

Sub TestEXP()
Dim varLen(2) As Variant
Dim i As Long, n As Long, b As Long
Dim FERow As Long, BERow As Long

With Sheets("Sheet7")
For i = 2 To 4
varLen(b) = .Cells(Rows.Count, i).End(xlUp).Row
b = b + 1
Next
BERow = WorksheetFunction.Max(varLen) + 1
MsgBox BERow
End With

With Sheets("Sheet8")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
FERow = WorksheetFunction.Max(varLen) + 1



Sheets("Sheet7").Range("B2:D" & BERow).Copy .Range("B" & FERow)
End With

End Sub

Hi Howard,

Am Mon, 17 Nov 2014 01:36:39 -0800 (PST) schrieb L. Howard:

So if the FERow works for the sheet8 range, I tried to use a BERow for the range on sheet7.

FERow (First Empty Row) is the last row + 1.
For the range to copy you don't need to add 1.
And for the next loop you have to reset n to 0:

Sub TestEXP()
Dim varLen(2) As Variant
Dim i As Long, n As Long
Dim FERow As Long, BERow As Long

With Sheets("Sheet7")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
BERow = WorksheetFunction.Max(varLen)
MsgBox BERow
End With

With Sheets("Sheet8")
n = 0
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
FERow = WorksheetFunction.Max(varLen) + 1

Sheets("Sheet7").Range("B2:D" & BERow).Copy .Range("B" & FERow)
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Monday, November 17, 2014 1:52:50 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Mon, 17 Nov 2014 01:36:39 -0800 (PST) schrieb L. Howard:

So if the FERow works for the sheet8 range, I tried to use a BERow for the range on sheet7.

FERow (First Empty Row) is the last row + 1.
For the range to copy you don't need to add 1.
And for the next loop you have to reset n to 0:

Sub TestEXP()
Dim varLen(2) As Variant
Dim i As Long, n As Long
Dim FERow As Long, BERow As Long

With Sheets("Sheet7")
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
BERow = WorksheetFunction.Max(varLen)
MsgBox BERow
End With

With Sheets("Sheet8")
n = 0
For i = 2 To 4
varLen(n) = .Cells(Rows.Count, i).End(xlUp).Row
n = n + 1
Next
FERow = WorksheetFunction.Max(varLen) + 1

Sheets("Sheet7").Range("B2:D" & BERow).Copy .Range("B" & FERow)
End With

End Sub


Regards
Claus B.
--


Got it.

Works very nice.

Thanks Claus.

Howard