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

  • Thread starter Thread starter L. Howard
  • Start date Start date
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
 
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
Click to expand...

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
Click to expand...

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!!
Click to expand...

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
 
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
Click to expand...

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.
Click to expand...

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.
 
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
 
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.
--
Click to expand...

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.
Click to expand...

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.
 
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.
--
Click to expand...


Got it.

Works very nice.

Thanks Claus.

Howard
 
Back
Top