Userform - paste data next available row

akash.scap

New Member
Joined
Feb 8, 2011
Messages
20
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,309
Office Version
  1. 365
Platform
  1. Windows
What happens if there is data in all the rows? Where does the new data go?
 

akash.scap

New Member
Joined
Feb 8, 2011
Messages
20
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

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
This might be what you're looking for;

Code:
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
 

Forum statistics

Threads
1,144,698
Messages
5,725,822
Members
422,643
Latest member
elwayfan446

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top