Vba excel как найти пустые строки

If you mean the row number after the last row that is used, you can find it with this:

Dim unusedRow As Long
unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row

If you mean a row that happens to be blank with data after it… it gets more complicated.

Here’s a function I wrote which will give you the actual row number of the first row that is blank for the provided worksheet.

Function firstBlankRow(ws As Worksheet) As Long
'returns the row # of the row after the last used row
'Or the first row with no data in it
    Dim rw As Range
    For Each rw In ws.UsedRange.Rows
        If rw.Address = ws.Range(rw.Address).SpecialCells(xlCellTypeBlanks). _
            Address Then

                firstBlankRow = rw.Row
                Exit For
        End If
    Next
    If firstBlankRow = 0 Then
        firstBlankRow = ws.Cells.SpecialCells(xlCellTypeLastCell). _
                    Offset(1, 0).Row
    End If
End Function

Usage example: firstblankRow(thisworkbook.Sheets(1)) or pass any worksheet.

Edit: As ooo pointed out, this will error if there are no blank cells in your used range.

answered Sep 19, 2012 at 15:35

I would have done it like this. Short and sweet 🙂

Sub test()
Dim rngToSearch As Range
Dim FirstBlankCell As Range
Dim firstEmptyRow As Long

Set rngToSearch = Sheet1.Range("A:A")
    'Check first cell isn't empty
    If IsEmpty(rngToSearch.Cells(1, 1)) Then
        firstEmptyRow = rngToSearch.Cells(1, 1).Row
    Else
        Set FirstBlankCell = rngToSearch.FindNext(After:=rngToSearch.Cells(1, 1))
        If Not FirstBlankCell Is Nothing Then
            firstEmptyRow = FirstBlankCell.Row
        Else
            'no empty cell in range searched
        End If
    End If
End Sub

Updated to check if first row is empty.

Edit: Update to include check if entire row is empty

Option Explicit

Sub test()
Dim rngToSearch As Range
Dim firstblankrownumber As Long

    Set rngToSearch = Sheet1.Range("A1:C200")
    firstblankrownumber = FirstBlankRow(rngToSearch)
    Debug.Print firstblankrownumber

End Sub

Function FirstBlankRow(ByVal rngToSearch As Range, Optional activeCell As Range) As Long
Dim FirstBlankCell As Range

    If activeCell Is Nothing Then Set activeCell = rngToSearch.Cells(1, 1)
    'Check first cell isn't empty
    If WorksheetFunction.CountA(rngToSearch.Cells(1, 1).EntireRow) = 0 Then
        FirstBlankRow = rngToSearch.Cells(1, 1).Row
    Else

        Set FirstBlankCell = rngToSearch.FindNext(After:=activeCell)
        If Not FirstBlankCell Is Nothing Then

            If WorksheetFunction.CountA(FirstBlankCell.EntireRow) = 0 Then
                FirstBlankRow = FirstBlankCell.Row
            Else
                Set activeCell = FirstBlankCell
                FirstBlankRow = FirstBlankRow(rngToSearch, activeCell)

            End If
        Else
            'no empty cell in range searched
        End If
    End If
End Function

answered Sep 19, 2012 at 16:52

Update

Inspired by Daniel’s code above and the fact that this is WAY! more interesting to me now then the actual work I have to do, i created a hopefully full-proof function to find the first blank row in a sheet. Improvements welcome! Otherwise, this is going to my library 🙂
Hopefully others benefit as well.

    Function firstBlankRow(ws As Worksheet) As Long
'returns the row # of the row after the last used row
'Or the first row with no data in it

    Dim rngSearch As Range, cel As Range

    With ws

        Set rngSearch = .UsedRange.Columns(1).Find("") '-> does blank exist in the first column of usedRange

        If Not rngSearch Is Nothing Then

            Set rngSearch = .UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks)

            For Each cel In rngSearch

                If Application.WorksheetFunction.CountA(cel.EntireRow) = 0 Then

                    firstBlankRow = cel.Row
                    Exit For

                End If

            Next

        Else '-> no blanks in first column of used range

            If Application.WorksheetFunction.CountA(Cells(.Rows.Count, 1).EntireRow) = 0 Then '-> is the last row of the sheet blank?

                '-> yeap!, then no blank rows!
                MsgBox "Whoa! All rows in sheet are used. No blank rows exist!"


            Else

                '-> okay, blank row exists
                firstBlankRow = .UsedRange.SpecialCells(xlCellTypeBlanks).Row + 1

            End If

        End If

    End With

End Function

Original Answer

To find the first blank in a sheet, replace this part of your code:

Cells(1, 1).Select
For Each Cell In ws.UsedRange.Cells
    If Cell.Value = "" Then Cell = Num
    MsgBox "Checking cell " & Cell & " for value."
Next

With this code:

With ws

    Dim rngBlanks As Range, cel As Range

    Set rngBlanks = Intersect(.UsedRange, .Columns(1)).Find("")

    If Not rngBlanks Is Nothing Then '-> make sure blank cell exists in first column of usedrange
        '-> find all blank rows in column A within the used range
        Set rngBlanks = Intersect(.UsedRange, .Columns(1)).SpecialCells(xlCellTypeBlanks)

        For Each cel In rngBlanks '-> loop through blanks in column A

            '-> do a countA on the entire row, if it's 0, there is nothing in the row
            If Application.WorksheetFunction.CountA(cel.EntireRow) = 0 Then
                num = cel.Row
                Exit For
            End If

        Next
    Else

        num = usedRange.SpecialCells(xlCellTypeLastCell).Offset(1).Row                 

    End If


End With

answered Sep 19, 2012 at 15:37

answered Oct 18, 2013 at 19:24

very old thread but .. i was lookin for an “easier”… a smaller code

i honestly dont understand any of the answers above 😀
– i´m a noob

but this should do the job. (for smaller sheets)

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

reads every cell in col 1 from bottom up and stops at first empty cell

intRow = 1
Do until objExcel.Cells(intRow, 1).Value = ""
   intRow = intRow + 1
Loop

then you can write your info like this

objExcel.Cells(intRow, 1).Value = "first emtpy row, col 1"
objExcel.Cells(intRow, 2).Value = "first emtpy row, col 2"

etc…

and then i recognize its an vba thread … lol

answered Jul 3, 2014 at 15:43

quantum285quantum285

1,0322 gold badges11 silver badges22 bronze badges