Row.Count is skipping rows

[JBDC]

I have created a userform with tabs whose names correspond with column headings. My goal is to send the form information to the next empty row of the column with the corresponding heading to that of the tab selected in the userform. My code is the following:

Dim nextRow As Long
Dim nextRow1 As Long
Dim SubName As String
Dim iColumnSub As Long
Dim wsB As Worksheet
Set wsB = Worksheets("Banking")
    
    SubName = MultiPage2.SelectedItem.Caption
    iColumnSub = wsB.Range("Z1:ZZ1").Find(What:=SubName, SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    nextRow = wsB.Range("Z" & Rows.Count).End(xlUp).Row + 1
 
    
    wsB.Cells(nextRow, iColumnSub).Value = TextBox14.Value

My dilemma:
Right now, only three columns are in play (though in the future there will be more). Entering data into columns 2 or 3 never poses a problem--the textbox entry falls correctly into the next empty row for the respective column. However, once data is entered into column 1, the following entry into either column 2 or 3 will not fall into the next empty row for that column--instead, a row is skipped. The result is that column 1 has a continuous list of entries and columns 2 and 3 have many skipped cells.

Finally, a new problem that has popped up is that once the data is entered, the cursor falls to a cell far from where the data was entered, leaving the user to scroll back to review the data entry into the worksheet.

Thanks for any help!

[Bernie Deitrick]

Try finding the next blank cell on the actual column of interest, instead of in Z. Change this:

    nextRow = wsB.Range("Z" & Rows.Count).End(xlUp).Row + 1

to this:

    nextRow = wsB.Cells(wsB.Rows.Count, iColumnSub).End(xlUp).Row + 1

And there is nothing in the code that you posted that changes the selection, so that issue must be elsewhere....

[JBDC]

Thanks! That did the trick!

As for the selection, I haven't specified it at any point in the code. This was not an issue until I made one small change this morning, which seems unrelated to me: I changed Dim SubName to String, where it was previously Long.

The only cell selection occurs earlier in the code. This userform sends data to two different worksheets, "Trading" and "Banking." The Userform is initiated in the Trading sheet, and there I have the following code:

ActiveSheet.Range(Cells(7, iColumn), Cells(28, iColumn)).Select
    Selection.Font.Color = -16776961

The problem you solved is in worksheet "Banking," where no cell selection is ever specified and data input is always specified with wsB (as in the code in previous post).

[Bernie Deitrick]

I'm not sure what would move the cursor to a distant cell, then, unless there is a selection. But your code:

ActiveSheet.Range(Cells(7, iColumn), Cells(28, iColumn)).Select
    Selection.Font.Color = -16776961

could apply to any sheet. Better would be

With Worksheets("SheetName") 'Or use sheet's codename, like Sheet1 or shtCodeNameYouApplied
.Range(.Cells(7, iColumn), .Cells(28, iColumn)).Font.Color = -16776961
End With

[JBDC]

I went halfway toward your suggestion with the following, just to experiment:

ActiveSheet.Range(Cells(7, iColumn), Cells(28, iColumn)).Font.Color = -16776961

And it worked. Thanks again!

[Bernie Deitrick]

Great - splitting the difference is often a workable solution