Last Row range

[ToniB]

Hi guys,
I hope you can help me with this. I am trying to select a range on varios sheets from cell A5 to the "last row" (in column A) and have "SPL" fill in. If I just select A5 it works, but when I try the range it does not. I hope you can help me! Here is the code below - I highlighted the row that is not working.


ws.Select
'Add 'TRNS' & 'ENDTRNS' to Col A
ws.Range("A4").Value = "TRNS" 'This adds 'TRNS' to A4
'#### This changes all of column A to 'TRNS' ####################
' intRowCount = Range("A4").CurrentRegion.Rows.Count + 3
' For i = 1 To intRowCount
' ActiveCell.Value = "TRNS"
' ActiveCell.Offset(1, 0).Select
' Next i
'################################################################
lr = Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A5" & lr).Value = "SPL"
ws.Range("A" & lr).Offset(1, 0).Value = "ENDTRNS"


Thank you!
ToniB

 

[Rick Rothstein]

Hi guys,
I hope you can help me with this. I am trying to select a range on varios sheets from cell A5 to the "last row" (in column A) and have "SPL" fill in. If I just select A5 it works, but when I try the range it does not. I hope you can help me! Here is the code below - I highlighted the row that is not working.


ws.Select
'Add 'TRNS' & 'ENDTRNS' to Col A
ws.Range("A4").Value = "TRNS" 'This adds 'TRNS' to A4
'#### This changes all of column A to 'TRNS' ####################
' intRowCount = Range("A4").CurrentRegion.Rows.Count + 3
' For i = 1 To intRowCount
' ActiveCell.Value = "TRNS"
' ActiveCell.Offset(1, 0).Select
' Next i
'################################################################
lr = Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A5" & lr).Value = "SPL"
ws.Range("A" & lr).Offset(1, 0).Value = "ENDTRNS"

You have not specified the range correctly. Try it this way...

ws.Range("A5:A" & lr).Value = "SPL"

 

[ToniB]

You have not specified the range correctly. Try it this way...

ws.Range("A5:A" & lr).Value = "SPL"

[Thank you Rick - I will try it now!]

 

[ToniB]

[It worked!!]