VBA checking if an entire row from A"N" to Z"N" is empty and put textbox value

Question

I was searching in many pages but couldn't find a way to find the first row in the range after the last used row and put the TextBox values from A"n" to Z"n" was possible, the only thing that I could find was to check only a single column. Hope someone would help.

 Private Sub CommandButton1_Click()
  
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
   'newRow = Application.WorksheetFunction.CountA(ws.Range("A:Z")) + 1 this was an experiment    

    ws.Cells(newRow, 2).Value = TVehicle.Value
    ws.Cells(newRow, 3).Value = TPlace.Value
    ws.Cells(newRow, 4).Value = TRange.Value
    ws.Cells(newRow, 5).Value = TOD.Value
    ws.Cells(newRow, 6).Value = TIC.Value
    ws.Cells(newRow, 7).Value = TEC.Value
    ecc...   

End Sub

I'm not 100% clear on what you're asking, but you might find that this [SO question](https://stackoverflow.com/questions/3628057/how-to-find-out-if-an-entire-row-is-blank-in-excel-thorough-vba) helps? — JohnM, Aug 01 '23 at 10:32
It looks like you're actually after the *last used* row, plus 1. Is that right? i.e. the next available empty row? — CLR, Aug 01 '23 at 10:48

score 1 · Accepted Answer · answered Aug 01 '23 at 12:14

1

To get the first empty row (i.e. without contents), but examining only columns A-Z, you can use the following:

FirstEmptyRow = ActiveSheet.Range("A:Z").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

This is on the assumption that columns A:Z have some data in them, it will error if the entire A:Z range is blank - so you should consider error trapping that.

answered Aug 01 '23 at 12:14

CLR

11,284
1
11
29

This gives a false result if the range content is not contiguous.. This is a possibility. – Black cat Aug 02 '23 at 20:58
Absolutely. This is based on OPs response to my comment on main question thread. – CLR Aug 03 '23 at 07:28

score 0 · Answer 2 · answered Aug 01 '23 at 13:05

Replace assignments with the actual ones.(Worksheet name and cell values)

Sub findemptyrow()
Set ws = Worksheets("Sheet6")
i = 1
Do Until WorksheetFunction.CountA(ws.Range("A" & i & ":Z" & i)) = 0
i = i + 1
Loop
Debug.Print "Empty line number is:", i

ws.Cells(i, "A") = "first value"
ws.Cells(i, "Z") = "last value"

End Sub

score 0 · Answer 3 · answered Aug 01 '23 at 13:56

0

Glue everything together.
If the length is zero, there is nothing. In formula form:

=LEN(CONCAT(A1:Z1))

This is just in formula-form, but I'm convinced it should not be too hard finding the corresponding VBA functions.

answered Aug 01 '23 at 13:56

Dominique

16,450
15
56
112

score 0 · Answer 4 · answered Aug 02 '23 at 06:44

Get First Available Row

Main

Private Sub CommandButton1_Click()
    
    Const DST_SHEET As String = "Sheet1"
    Const DST_FIRST_ROW As String = "A2:Z2"

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets(DST_SHEET)
    Dim NewRow As Long: NewRow = GetFirstRow(ws, DST_FIRST_ROW)
    
    ws.Cells(NewRow, 2).Value = TVehicle.Value
    ws.Cells(NewRow, 3).Value = TPlace.Value
    ws.Cells(NewRow, 4).Value = TRange.Value
    ws.Cells(NewRow, 5).Value = TOD.Value
    ws.Cells(NewRow, 6).Value = TIC.Value
    ws.Cells(NewRow, 7).Value = TEC.Value

End Sub

Help

Function GetFirstRow( _
    ByVal ws As Worksheet, _
    ByVal FirstRowAddress As String) _
As Long
    
    Dim FirstRow As Long
    
    With ws.Range(FirstRowAddress)
        FirstRow = .Row
        Dim lCell As Range:
        Set lCell = .Resize(.Worksheet.Rows.Count - FirstRow + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If Not lCell Is Nothing Then FirstRow = lCell.Row + 1
    End With

    GetFirstRow = FirstRow
    
End Function

VBA checking if an entire row from A"N" to Z"N" is empty and put textbox value

4 Answers4

Get First Available Row