VBA Last Row Question

[ummjay]

Hi!

I have some logic which will copy and paste into a range using last row. that works fine:

lastrow = ActiveSheet.Range("AY7:BB" & Rows.Count).Find("").Row - 1
Set CopyRng = Range("AY7:BB" & lastrow)
CopyRng.Copy
Range("AQ7:AT" & lastrow).PasteSpecial xlPasteValues

I then want to re-copy that and paste it below the paste values above. this also works fine:

'last row of actual data
lastrowadj = ActiveSheet.Range("AQ7:AT" & Rows.Count).Find("").Row
'copy original range/paste orignal range below 1st paste
CopyRng.Copy
Range("AQ" & lastrowadj & ":AT" & lastrowadj).PasteSpecial xlPasteValues

The issue I have is, then I want to replace cells on the 2nd data set I pasted, and that doesnt seem to be working properly. it only replaces the 1st line of that range, and not the ones beneath it:

'final last row data
lastrowfinal = ActiveSheet.Range("AQ7:AT" & Rows.Count).Find("").Row
range("AQ" & lastrowadj & ":AQ" & lastrowfinal).Replace What:="S", Replacement:="Sam", LookAt:=xlWhole
Range("AQ" & lastrowadj & ":AQ" & lastrowfinal).Replace What:="B", Replacement:="Boy", LookAt:=xlWhole

Any ideas?

 

[Joe4]

Check the values of "lastrowadj" and "lastrowfinal" when you get to those replacement lines to confirm they are what you think they are.
There are numerous ways to check those values:
- use a MsgBox to return their values
- go through the code line-by-line and hover over those variables when you get to that line to see what the values are
- use the Immediate Window

 

[MARK858]

lastrowfinal = ActiveSheet.Range("AQ7:AT" & Rows.Count).Find("").Row

That doesn't look like a very reliable way of finding the last row.

You would normally find the last row with data then offset it by one row

For instance see the results of the Message boxes with the data below and the 2 code lines (please note you still need to carry out the testing of your variables proposed by Joe4 in post 2)

Book1
	AQ	AR	AS	AT
7	Order ID	Order Date	Site	SP Name
8	108-4-N70	04/02/2014	On Site	Steven Buchanan
9	108-13-F86	13/02/2014	Off Site	Nancy Davolio
10	108-11-N82	11/02/2014	On Site	Margaret Peacock
11	107-22-N14	22/10/2013	On Site	Steven Buchanan
12	108-14-N32	14/01/2014	On Site	Andrew Fuller
13	108-15-N33	15/01/2014	On Site	Michael Suyama
14	106-1-F18	01/08/2013	Off Site	Nancy Davolio
15	110-8-F09	08/04/2014	Off Site	Andrew Fuller
16	110-6-N01	06/04/2014	On Site	Andrew Fuller
17	107-29-F22	29/10/2013	Off Site	Laura Callahan
18	102-23-N63	23/07/2012	On Site	Anne Dodsworth
19	106-19-N75	19/09/2013	On Site	Steven Buchanan
20	109-31-F89	31/03/2014	Off Site	Andrew Fuller
21		01/04/2014
22		02/04/2014
Sheet1

Sub xxxx()
MsgBox ActiveSheet.Range("AQ7:AT" & Rows.Count).Find("").Row
MsgBox Range("AQ7:AT" & Rows.Count).Find("*", , xlValues, , xlByRows, xlPrevious).Row
End Sub

and even worse if there are any blank cells in the block of data

Book1
	AQ	AR	AS	AT
7	Order ID	Order Date	Site	SP Name
8	108-4-N70	04/02/2014	On Site	Steven Buchanan
9	108-13-F86	13/02/2014	Off Site	Nancy Davolio
10	108-11-N82	11/02/2014	On Site	Margaret Peacock
11	107-22-N14	22/10/2013	On Site	Steven Buchanan
12	108-14-N32	14/01/2014	On Site	Andrew Fuller
13	108-15-N33	15/01/2014	On Site	Michael Suyama
14	106-1-F18		Off Site	Nancy Davolio
15	110-8-F09	08/04/2014	Off Site	Andrew Fuller
16	110-6-N01	06/04/2014	On Site	Andrew Fuller
17	107-29-F22	29/10/2013	Off Site	Laura Callahan
18	102-23-N63	23/07/2012	On Site	Anne Dodsworth
19	106-19-N75	19/09/2013	On Site	Steven Buchanan
20	109-31-F89	31/03/2014	Off Site	Andrew Fuller
21		01/04/2014
22		02/04/2014
Sheet1

 

[ummjay]

thanks, yea, so I think the problem I'm having specifically, is being able to determine the 2nd range I made/pasted, and doing a find/replace on certain values within that range, and NOT the 1st range I pasted. I was trying to say lastrowfinal , is end of both data ranges combined to determine the end, and using lastrowadj as the START of the 2nd data range (or technically end of the 1st).

hope that's not confusing, is there a better way to do that?

 

[MARK858]

and NOT the 1st range I pasted

You are using the same method in each piece of code that you posted. Have you checked what your variables are returning as Joe4 suggested?

 

[ummjay]

I get a value of 13 in the msg box, but not sure what that actually relates to:

'last row of actual data
lastrowadj = ActiveSheet.Range("AQ6:AT" & Rows.Count).Find("").Row
MsgBox ActiveSheet.Range("AQ6:AT" & Rows.Count).Find("").Row

the data it pasted is correct, which should be 6, AQ7:A12, unless it counts every cell in the entire range? from AQ7:AT12? In that case, should the number be count 24?

 

[MARK858]

but not sure what that actually relates to:

The first blank cell in the range "AQ6:AT" & Rows.Count going in a horizontal zig-zag starting from cell AQ6

Not knowing what your data looks like, what happens with the code below (Please note the code below uses AQ7 whereas you have changed it to AQ6 in your last code, adjust if necessary)

Sub ummjay()
    Dim lastrow As Long, lastrowadj As Long, lastrowfinal As Long
    Dim CopyRng As Range

    lastrow = Range("AY7:BB" & Rows.Count).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set CopyRng = Range("AY7:BB" & lastrow)
    CopyRng.Copy
    Range("AQ7").PasteSpecial xlPasteValues

    lastrowadj = Range("AQ7:AT" & Rows.Count).Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
    Range("AQ" & lastrowadj).PasteSpecial xlPasteValues

    Application.CutCopyMode = False

    lastrowfinal = Range("AQ7:AT" & Rows.Count).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Range("AQ" & lastrowadj & ":AQ" & lastrowfinal).Replace "S", "Sam", xlWhole
    Range("AQ" & lastrowadj & ":AQ" & lastrowfinal).Replace "B", "Boy", xlWhole
End Sub

 

[MARK858]

Important: Please see edited code above

 

[ummjay]

worked like a charm! thanks!

 

[MARK858]

You're welcome