Autofill next row, but stop filling at certain row

Guard913

Board Regular
Joined
Apr 10, 2016
Messages
144
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
VBA Code:
    Dim Lastrow As Long, ws As Worksheet

    Set ws = Sheets("Emails")

    Lastrow = ws.Range("A" & Rows.count).End(xlUp).Row + 1 'Finds the last blank row
    
    ws.Range("A" & Lastrow).Value = TextBox7.Text
    ws.Range("B" & Lastrow).Value = TextBox8.Text
    ws.Range("C" & Lastrow).Value = TextBox9.Text

This works great on a blank sheet, but i am trying to create another code that does the same thing, but it starts the first row at A10 filling A10 B10 C10 with the items typed and then the next items typed go to A11 B11 C11 but here is the kicker i want it to stop doing anything after A13 B13 C13 are filled. At somepoint I would clear (all those boxes as a group and want it to restart back at A10 B10 C10 (but i don't need nor want that particular code in this, the clearing is handled by another code all together) i just want it to stop filling rows after A13 B13 C13 are filled, and then just wait until A10 B10 C10-A13 B13 C13 are clear.

This is going to run after a certain box is exited.


Ideas?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
How about
VBA Code:
   LastRow = Ws.Range("A" & Rows.Count).End(xlUp).Row + 1
   If LastRow < 10 Then LastRow = 10
   If LastRow > 13 Then Exit Sub
    
   Ws.Range("A" & LastRow).Value = TextBox7.Text
 
Upvote 0
How about
VBA Code:
   LastRow = Ws.Range("A" & Rows.Count).End(xlUp).Row + 1
   If LastRow < 10 Then LastRow = 10
   If LastRow > 13 Then Exit Sub
   
   Ws.Range("A" & LastRow).Value = TextBox7.Text

Sort of works, the thing is the reason i have it set into a certain cell and need it to stop after 4 is because there are other text around it i don't want it to mess up... (I have a 30+ Userform 15+ Sheet & Several 100+ macros in the excel file, and i am trying to reduce the size by combining the sheets. This works fine on an empty sheet though! Just need it to work even with other text around it. (maybe my initial code needs to be completely changed?)
 
Upvote 0
Ok, how about
VBA Code:
   Dim Lastrow As Long, ws As Worksheet

    Set ws = Sheets("Emails")
    On Error Resume Next
    Lastrow = ws.Range("A10:A13").SpecialCells(xlBlanks)(1).Row
    On Error GoTo 0
    If Lastrow = 0 Then Exit Sub
    ws.Range("A" & Lastrow).Value = TextBox7.Text
    ws.Range("B" & Lastrow).Value = TextBox8.Text
    ws.Range("C" & Lastrow).Value = TextBox9.Text
 
Upvote 0
THAT IS PERFECT!!!! OMG!!! YOU ARE AWESOME!!! Well, now I can combine so many sheets!!!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top