Userform - Input data ABOVE Total Row

[Robby19]

Hello again!

I have a userform that adds data to the first empty row within a certain range. What this does now, if there is a Total Row in my table, is adds the data under the total row. This means it is not being added to my table.

Is there a way to adjust my current code to add the data to the last row in my table, but above the total row?

Private Sub cmdbtnSave_Click()

'Error if Date not formatted correctly
If Not IsDate(Me.txtDate.Text) Then
    MsgBox "The Data entered is not a Date." & Chr(10) & "Please re-format it as appropriate."
        Exit Sub
End If

Dim emptyRow As Long

'Make Sheet3 (Account Register) active
 Sheet3.Activate

'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1

'Transfer information
 Cells(emptyRow, 2).Value = combAccount.Value
 Cells(emptyRow, 3).Value = txtDate.Value
 Cells(emptyRow, 4).Value = txtRef.Value
 Cells(emptyRow, 5).Value = txtPayee.Value

If optbtnYes.Value = True Then
     Cells(emptyRow, 6).Value = "Yes"
Else
     Cells(emptyRow, 6).Value = "No"
End If

 Cells(emptyRow, 7).Value = combCategory.Value
 Cells(emptyRow, 8).Value = txtMemo.Value

If combStatus.Value = "Reconciled" Then
     Cells(emptyRow, 9).Value = "R"
Else
    If combStatus.Value = "Cleared" Then
        Cells(emptyRow, 9).Value = "C"
Else
     Cells(emptyRow, 9).Value = "V"
    End If
End If

If btnIncome.Value = False Then
     Cells(emptyRow, 10).Value = txtIncome
Else
     Cells(emptyRow, 11).Value = txtIncome
End If

    Cells(emptyRow, IIf(btnIncome.Value, 11, 10)).Value = CCur(Val(txtIncome.Value))

'Automatic sorting
Dim ws As Worksheet
    Set ws = ActiveSheet
Dim target_table As ListObject
    Set target_table = ws.ListObjects("AccountRegister")
Dim sort_column_index As Long
    sort_column_index = target_table.ListColumns("Date").Index
Dim sort_column As Range
    Set sort_column = target_table.ListColumns(sort_column_index).Range

'Apply the sorting to the table
With target_table.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sort_column _
        , SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    .Apply
End With

ActiveSheet.Protect "", True, True, AllowFiltering:=True, AllowSorting:=True

Unload Me

End Sub

 

[Fluff]

Maybe

emptyRow = WorksheetFunction.CountA(Range("B:B"))
Rows(emptyRow).Insert
'Transfer information
Cells(emptyRow, 2).Value = combAccount.Value