Insert line in second to the last row.

[Livin404]

Greetings, I'm polishing up my project and I discovered by VBA does not work on an Excel Table, but it is just fine on a normal worksheet. The long story short is if I can insert a line in the second to the last line that is filled it should take care of my blank issues on my pivot tables/splicers. I'm thinking it will keep "my data source" updated. Having to use the whole worksheet is not streamlining my project. I figured second to the last line will continuing to expand the data source would do the same thing as a excel table without totally rewriting everything else. I have "Descend" and "Refresh Buttons" which keeps things in order.

This is working with a UserForm to input data in a database.



My code is

Sub Submit2()
Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("Pallets")

If Palletform.txtRowNumber2.Value = "" Then


iRow = [Counta(Pallets!A:A)] + 1 

 Else
   iRow = Palletform.txtRowNumber2.Value

   End If
With sh
     If Palletform.txtDate <> "" Then .Cells(iRow, 1).Value = CDate(Palletform.txtDate)
     .Cells(iRow, 2) = Palletform.txtDestination.Value
     .Cells(iRow, 3) = Palletform.txtPalletID.Value
     .Cells(iRow, 4) = Palletform.txtTcns.Value
     .Cells(iRow, 5) = Palletform.txtPieces.Value
     .Cells(iRow, 6) = Palletform.txtWeight.Value
     .Cells(iRow, 7) = Palletform.cmbShipment.Value
     .Cells(iRow, 9) = Palletform.cmbSupport.Value
     .Cells(iRow, 10) = Palletform.txtRemarks.Value

  End With

End Sub

Thank you,

 

[Livin404]

Greetings, I'm polishing up my project and I discovered by VBA does not work on an Excel Table, but it is just fine on a normal worksheet. The long story short is if I can insert a line in the second to the last line that is filled it should take care of my blank issues on my pivot tables/splicers. I'm thinking it will keep "my data source" updated. Having to use the whole worksheet is not streamlining my project. I figured second to the last line will continuing to expand the data source would do the same thing as a excel table without totally rewriting everything else. I have "Descend" and "Refresh Buttons" which keeps things in order.

This is working with a UserForm to input data in a database.



My code is

Sub Submit2()
Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("Pallets")

If Palletform.txtRowNumber2.Value = "" Then


iRow = [Counta(Pallets!A:A)] + 1

 Else
   iRow = Palletform.txtRowNumber2.Value

   End If
With sh
     If Palletform.txtDate <> "" Then .Cells(iRow, 1).Value = CDate(Palletform.txtDate)
     .Cells(iRow, 2) = Palletform.txtDestination.Value
     .Cells(iRow, 3) = Palletform.txtPalletID.Value
     .Cells(iRow, 4) = Palletform.txtTcns.Value
     .Cells(iRow, 5) = Palletform.txtPieces.Value
     .Cells(iRow, 6) = Palletform.txtWeight.Value
     .Cells(iRow, 7) = Palletform.cmbShipment.Value
     .Cells(iRow, 9) = Palletform.cmbSupport.Value
     .Cells(iRow, 10) = Palletform.txtRemarks.Value

  End With

End Sub

Thank you,

The name of the table is "table 1".

 

[Livin404]

Thank you, I do have the situation resolved. I continue to work on these, even while I hope an answer may appear. Much appreciated.