Userform - paste data next available row

[akash.scap]

Hello, i have a created a userform to input data in the next blank row on sheet1, however the range limited (A1 to A40). Basically i need data to be pasted in the next blank row (again the range should not exceed A40 as i have data after A40.

Currently i have a macro which works...but sometimes pastes data all the way down i.e. A101 etc...

In simple - when i put data in my userform i need data to search for next blank row in range A1 to A40 and paste it there.

Sorry for being repetitive..trying to make sense.
Please help...thank you

 

[Norie]

What happens if there is data in all the rows? Where does the new data go?

 

[akash.scap]

Sorry..data wont be more than A40. I have set A40 to be max anyway...my data usually is usually under A25..i just left some space..in case i need the extra rows. There always will be blanks rows available...Please help

 

[rs2k]

This might be what you're looking for;

Private Sub cmdSub_Click()

Dim irow As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")

irow = ws.Range("A40").End(xlUp).Offset(1, 0).Row

Application.ScreenUpdating = False

With ws
.Range("A" & irow) = TextBox1.Value

End With
TextBox1.Value = ""

Application.ScreenUpdating = True
Unload Me

End Sub

HTH
Colin