New row isn't being added onto my table

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
Hi there,

I have a spreadsheet which uses a userform to enter data on to a sheet called "Readings".
Everything seems to work fine except for a couple of things:

- It misses out the first available line in my table
- My table does not expand to encompass the new line

I have checked my autocorrect settings and everything seems to be in order there.
Below is the code that I am using:

Code:
 Dim rw As Long    'next available row 
 Dim ws As Worksheet
        Set ws = Worksheets("Readings")
   
 
      'get the next avialable row in Sheet1
      rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 
      'put the text box values in this row
      With ws
      
      .Cells(rw, "A").Value = Me.TBID.Value
      .Cells(rw, "B").Value = Me.TBDate.Value
      .Cells(rw, "C").Value = Me.TBReason.Value
      .Cells(rw, "D").Value = Me.CBoxSpread.Value
      
      
      .Cells(rw, "E").Value = Comboxnumber2.Text & " OF " & Comboxminor2.Text & "; " & Comboxarea2.Text
      .Cells(rw, "F").Value = TBanswer2.Value
      
      .Cells(rw, "G").Value = Comboxnumber3.Text & " OF " & Comboxminor3.Text & "; " & Comboxarea3.Text
      .Cells(rw, "H").Value = TBanswer3.Value
      
      .Cells(rw, "I").Value = Comboxnumber4.Text & " OF " & Comboxminor4.Text & "; " & Comboxarea4.Text
      .Cells(rw, "J").Value = TBanswer4.Value
      
      .Cells(rw, "K").Value = Comboxnumber5.Text & " OF " & Comboxminor5.Text & "; " & Comboxarea5.Text
      .Cells(rw, "L").Value = TBanswer5.Value
      
      .Cells(rw, "M").Value = Comboxnumber6.Text & " OF " & Comboxminor6.Text & "; " & Comboxarea6.Text
      .Cells(rw, "N").Value = TBanswer6.Value
      
      .Cells(rw, "O").Value = Comboxnumber7.Text & " OF " & Comboxminor7.Text & "; " & Comboxarea7.Text
      .Cells(rw, "P").Value = TBanswer7.Value
      
      .Cells(rw, "Q").Value = Comboxnumber8.Text & " OF " & Comboxminor8.Text & "; " & Comboxarea8.Text
      .Cells(rw, "R").Value = TBanswer8.Value
      
      .Cells(rw, "S").Value = Comboxnumber9.Text & " OF " & Comboxminor9.Text & "; " & Comboxarea9.Text
      .Cells(rw, "T").Value = TBanswer9.Value
      
      .Cells(rw, "U").Value = Comboxnumber10.Text & " OF " & Comboxminor10.Text & "; " & Comboxarea10.Text
      .Cells(rw, "V").Value = TBanswer10.Value
      
      .Cells(rw, "W").Value = Comboxnumber11.Text & " OF " & Comboxminor11.Text & "; " & Comboxarea11.Text
      .Cells(rw, "X").Value = TBanswer11.Value
      
      
 
      
   End With

Many thanks in advance for any help you can give,

Mike
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Is your data in a real table or just a range?
If it is a real table, what's the name?
 
Upvote 0
Hi There,

I'm really sorry but I didn't realise someone had responded.

It is in a table called "Data".

Mike
 
Upvote 0
Try replacing this line:
Code:
 rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
with this:
Code:
      ws.ListObjects("Data").ListRows.Add
       rw = Split(ws.ListObjects("Data").DataBodyRange.Address, "$")(4)
 
Upvote 0
Hi Akuini,

I tried changing the code but comes up with a runtime error saying that the subscript is out of range. Not sure what this means. Have checked and the name of my table is definitely "Data".

Thanks,

Mike
 
Upvote 0
It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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