Solved: apply end of cell to paste

[rayoconnell]

Hello All,

im trying to count how many lines are in my spreadsheet( this bit works)
then when i have that count i am trying to apply that count to the end of my "copy function" the 3rd last line"Range("C2:CintNosRows")".
i have dimed the count as intNosRows and tryed to put that in instead of a constant number due to the fact every sheet varys in size.
As usual any help at all would be great!!!!!!

Select

Private Sub CommandButton8_Click()
' Macro1 Macro
' Macro recorded 5/15/2007 by Ray O'Connell
'
'
Dim intNosRows As Integer, CellAddress As String, Strbuffer As String, i As Integer
Strbuffer = CStr(Range("A1").Value)
i = 1
'Find how far we have to go
Do
If Strbuffer <> vbNullString Then
intNosRows = intNosRows + 1
End If
i = i + 1
CellAddress = "A" & CStr(i)
Strbuffer = CStr(Range(CellAddress).Value)

Loop Until Strbuffer = vbNullString
'Reset counter
i = 0

Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[5]=""Y"",""VESSEL"")"
ActiveCell.FormulaR1C1 = "=IF(RC[5]=""Y"",""VESSEL"")"
Range("C2").Select
Selection.Copy
Range("C2:CintNosRows").Select
ActiveSheet.Paste

End Sub

[rayoconnell]

I know im replying to my own query but i got a response on another site
this is it

Private Sub CommandButton8_Click()
Dim LstRwA As Long
LstRwA = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2").FormulaR1C1 = "=IF(RC[5]=""Y"",""VESSEL"")"
Range("C2").Copy Range("C2:C" & LstRwA)
End Sub

[Simon Lloyd]

Maybe this will help you understand counting rows a little
[VBA]
Sub Used_Row_Count()
Dim C As Integer
Dim Ca, Ra As String
C = ActiveSheet.UsedRange.Rows.Count
Ca = ActiveSheet.UsedRange.Columns.Count
Ra = ActiveSheet.UsedRange.Address
MsgBox "This many rows are used " & C & Chr(13) _
& "This is how many columns are used " & Ca & Chr(13) _
& "This is the address of the whole used range" & Ra, vbOKOnly, "Range addresses"
End Sub
[/VBA]

[Simon Lloyd]

Ray you should always post the link to cross posts both here and the other site, it's unfair to have two lots of people using up their valuable free time and coming to the same solution when they could e helping other posters!

[rayoconnell]

thank you simon it does help,
as you can see from my code i am only learning,
any help is great.