Userform - Input data ABOVE Total Row

Robby19

Board Regular
Joined
Mar 19, 2018
Messages
227
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?

Code:
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
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Maybe
Code:
emptyRow = WorksheetFunction.CountA(Range("B:B"))
Rows(emptyRow).Insert
'Transfer information
Cells(emptyRow, 2).Value = combAccount.Value
 
Upvote 0

Forum statistics

Threads
1,223,708
Messages
6,174,006
Members
452,542
Latest member
Bricklin

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