Paste in column B using Last row of either column B, C or D.

L

L. 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
 
L

L. 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

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
 
G

GS

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
 
L

L. 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!!

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
 
G

GS

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
 
C

Claus Busch

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.
 
G

GS

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
 
L

L. Howard

Hi Howard,

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


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
 
L

L. Howard

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
 
C

Claus Busch

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.
 
L

L. Howard

Hi Howard,

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top