Search code examples
excelvbauserform

How do i fix application.counta formula

I am writing a user-form to input data into an excel spreadsheet and i am having issues with the counta command to register a blank line.

I am new to VBA and tried looking for alternatives online without much success, and honestly not knowing what i was looking at when reading some of the code.

Please see my code below and for some context it is for room management, tracking guests arrival and departure dates, room rates and if two rooms are utilized next to each other. For some reason the data input begins at row 25 and i can't figure out for the life of me why when there are 25 blank rows above the cell being registered:

Dim emptyRow As Long


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


Cells(emptyRow, 2).Value = txtRoomNum.Value
Cells(emptyRow, 3).Value = txtGuestName.Value
Cells(emptyRow, 4).Value = DTPicker1.Value
Cells(emptyRow, 5).Value = DTPicker2.Value

If opInter.Value = True Then
Cells(emptyRow, 7).Value = (Val(txtRev1.Text) + Val(txtRev2.Text) + Val(txtRev3.Text)) * 0.5

Cells(emptyRow, 12).Value = Val(txtChildren.Text) * 0.5
Cells(emptyRow, 13).Value = Val(txtAdults.Text) * 0.5

Else
Cells(emptyRow, 7).Value = (Val(txtRev1.Text) + Val(txtRev2.Text) + Val(txtRev3.Text))

Cells(emptyRow, 12).Value = txtChildren.Value
Cells(emptyRow, 13).Value = txtAdults.Value

End If

Cells(emptyRow, 8).Value = UCase(txtRateCode.Value)


If opRoomOnly.Value = True Then
Cells(emptyRow, 10).Value = "Yes"

Else

Cells(emptyRow, 10).Value = "No"

End If

If opInter = True Then
Cells(emptyRow, 11).Value = "Yes"

Else

Cells(emptyRow, 11).Value = "No"
End If

Unload Me

End Sub```
Solution

  • The function COUNTA() returns the number of cells that are not blank in a given range.

    I assume you want to paste data on the first empty row in the sheet, so you need the number of the last non-empty row (+1) to paste the data in. Try this:

    emptyRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

    Here are some more examples on how to find last row / column in a sheet. https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba