Mismatch error in CountA(Range("A:A")) 1?-CodePudding

I'm new at VBA in Excel, and I'm trying to figure out the solution for iRow = WorksheetFunction.CountA([Funnel_Database!"A:A"]) 1. I'm not sure if the issue is that one of the input fields is an integer. Any help will do, thank you!

Option Explicit

Sub Reset()
    Dim iRow As Long
    
    iRow = [counta(Funnel_Database!A:A)] ' Identifying the last row
    
    With JazForm ' Writing code to make everything clear
    
    .txtFirstName = ""
    .txtLastName = ""
    .txtPhone = ""
    .txtEmail = ""
    
    .lstFunnel_Database.ColumnCount = 6
    .lstFunnel_Database.ColumnHeads = True
    
    If iRow > 1 Then
    
        .lstFunnel_Database.RowSource = "Funnel_Database!A2:I" & iRow
    Else
    
        .lstFunnel_Database.RowSource = "Funnel_Database!A2:I2"
    
    End If
    
    End With


End Sub

Sub Submit()

Dim sh As Worksheet
Dim iRow As Long


Set sh = ThisWorkbook.Sheets("Funnel_Database")

iRow = WorksheetFunction.CountA([Funnel_Database!"A:A"])   1


With sh

    .Cells(iRow, 1) = iRow - 1
    
    .Cells(iRow, 2) = JazForm.txtFirstName.Value
    
    .Cells(iRow, 3) = JazForm.txtLastName.Value
    
    .Cells(iRow, 4) = JazForm.txtPhone.Value
    
    .Cells(iRow, 5) = JazForm.txtEmail.Value
    
    .Cells(iRow, 6) = [Text(Now(), "MM-DD-YY HH:MM:SS")]
     

End With


End Sub

Sub Show_Form()

    JazForm.Show
    
End Sub

CodePudding user response：

Try:

iRow = WorksheetFunction.CountA(sh.range("A:A"))   1

However, a better alternative (and one you'll see all over this website) might be:

iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row   1

Instead of using CountA - which can have issues if some of the cells are blank, this gives you the equivalent of moving the cursor to the very bottom cell of a column and then hitting CTRL-UP - which locates the last cell containing data in that column. You then correctly add 1 to locate the first available empty cell after that.