New row isn't being added onto my table

[Mikeymike_W]

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:

 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

 

[Akuini]

Is your data in a real table or just a range?
If it is a real table, what's the name?

 

[Mikeymike_W]

Hi There,

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

It is in a table called "Data".

Mike

 

[Akuini]

Try replacing this line:

 rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

with this:

      ws.ListObjects("Data").ListRows.Add
       rw = Split(ws.ListObjects("Data").DataBodyRange.Address, "$")(4)

 

[Mikeymike_W]

I'll try this out first thing tomorrow, thank you so much

 

[Mikeymike_W]

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

 

[Akuini]

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