CODEWITHSUNDEEP
vbaexcel
user6592399
user6592399

Reputation: 23

Find the first empty row after the given row number in Excel VBA

I need to find the first empty row number after the given row number.

please check the image below

Please check the image

for e.g: assume, my current row number is 6 then my output should be 10.

Upvotes: 1

Views: 6120

Answers (4)

Benno Grimm
Benno Grimm

Reputation: 530

Dim startRow As Long
Dim i As Long
Dim lastRow As Long
Dim sh As Worksheet


startRow = 2  'Set first row to check
Set sh = ThisWorkbook.Worksheets("Sheet1")

lastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row

For i = startRow To lastRow
    If sh.Cells(i, 1) = "" Then
        MsgBox "First empty row: " & i
        Exit For
    End If
Next i

Have you tried something like this?

Note: This won't show you when the last row is empty.

Upvotes: 2

user6432984
user6432984

Reputation:

I use CountA to see if the entire row is empty. 

   Function FirstEmtpyRow(startRow As Long) As Long

        Do
            startRow = startRow + 1
            If startRow = rpws.Count Then Exit Function

        Loop Until WorksheetFunction.CountA(Rows(startRow)) = 0

        FirstEmtpyRow = startRow

    End Function

Upvotes: 0

user4039065
user4039065

Reputation:

You can use .End(xlDown) but you have to be careful that the immediately next cell is not blank or you could skip over it.

dim rw as long, nrw as long
rw = 6
with worksheets("sheet1")
    with .cells(rw, "A")
        if IsEmpty(.Offset(1, 0)) then
            nrw = rw + 1
        else
            nrw = .end(xldown).offset(1, 0).row
        end if
    end with
end with

debug.print nrw

Upvotes: 0

avplol
avplol

Reputation: 88

Something like this? 

function FindFirstEmpty(i as long)

while cells(i,1).value <> ""
    i = i + 1
wend

FindFirstEmpty = i

End function

Depends how you are obtaining the row from which to begin.

Upvotes: 2

Related Questions