Finding first blank row, then writing to it

Question

I need to find the first blank row in a workbook and write information to (row, 1) and (row, 2). I think I'm currently pretty stuck...

Function WriteToMaster(num, path) As Boolean

'Declare variables
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim infoLoc As Integer

Set xlApp = New Excel.Application

Set wb = xlApp.Workbooks.Open("PATH OF THE DOC")
Set ws = wb.Worksheets("Sheet1")

'Loop through cells, looking for an empty one, and set that to the Num
Cells(1, 1).Select
For Each Cell In ws.UsedRange.Cells
    If Cell.Value = "" Then Cell = Num
    MsgBox "Checking cell " & Cell & " for value."
Next


'Save, close, and quit
wb.Save
wb.Close
xlApp.Quit

'Resets the variables
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

Thanks so much for any help.

This has been covered many times. Exact duplicate of [Last not empty cell in row; Excel VBA](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) — brettdj, Sep 19 '12 at 23:14
@brettdj There's some overlap but I certainly wouldn't say it's an exact duplicate. okapishomapi is asking about finding the first blank row in a worksheet. Anonymous12345 is asking about finding the last non-populated cell in a row. — StockB, May 27 '14 at 15:23

Daniel · Accepted Answer · 2012-09-20T23:41:05.433

14

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.

edited Sep 20 '12 at 23:41

answered Sep 19 '12 at 15:35

Daniel

12,982
3
36
60

this won't necessarily find the first blank row, as there could be 1 or more fully blank rows in a between data sets in a worksheet. – Scott Holtzman Sep 19 '12 at 15:39
@ScottHoltzman Indeed, I added a partial answer while I wrote the rest. :-) – Daniel Sep 19 '12 at 15:42
1

got it. Its funny how two programmers approach the same task a little differently... – Scott Holtzman Sep 19 '12 at 15:45
@ScottHoltzman To be completely honest, I hadn't done it before so I had to figure it out. I just wanted to post a partial answer incase he actually only wanted what I first offered and was refreshing the page anxiously awaiting his answer. – Daniel Sep 19 '12 at 15:50
Yeah. I meant it in a good way. This is a great place for new ideas and to see how other programmers solve the same problem. I like your function. – Scott Holtzman Sep 19 '12 at 15:53
You have to be careful using `.SpecialCells(xlCellTypeBlanks)` pre Excel 2010 http://support.microsoft.com/default.aspx?scid=kb;en-us;832293 – Sep 19 '12 at 16:18
@ooo Thanks for the info. The document indicates it happens when you select, does it also occur when you only reference? Perhaps I should try to refactor this without using SpecialCells? – Daniel Sep 19 '12 at 16:23
To @ooo and Daniel (and OP) see my edited answer below. I wrote a comprehensive (I think) function, for this. Let me know what you think ... – Scott Holtzman Sep 19 '12 at 16:40
@DanielCook - Your code throws a runtime error 1004 if a row in the usedrange isn't blank. – Sep 20 '12 at 21:55
@ooo You're so getting a +1 for that. I'll just leave it as a warning instead of fixing it, since there's so many other good answers. – Daniel Sep 20 '12 at 23:36
Warning! The first code will not account for rows at the end that once contained data but are now blank. – ivan_pozdeev Jul 09 '17 at 05:05

score 6 · Answer 2 · 2012-09-20T19:11:24.880

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

I like it, but this only checks the first column which doesn't actually meet the requirements of the OP. — Daniel, Sep 20 '12 at 18:37

Scott Holtzman · Answer 3 · 2012-09-19T17:24:59.243

3

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

edited Sep 19 '12 at 17:24

answered Sep 19 '12 at 15:37

Scott Holtzman

27,099
5
37
72

2

This fails to assign `num` if all rows are used. – Daniel Sep 19 '12 at 15:57
Thank you, Scott! @Daniel - Does Excel not allocate new rows when the current ones are used up? – okapishomapi Sep 19 '12 at 16:03
@okapishomapi Of course it allows new rows. I'm saying that his code will not determine the next blank row, if no row in the usedrange is blank. Because it doesn't make a check after reviewing the usedrange to assure it found a blank row. – Daniel Sep 19 '12 at 16:09
@DanielCook What I meant is - I thought Excel automatically made new rows when the rest were used up (too much time with Google spreadsheets on my part). Playing around with Excel, that's obviously wrong. Plus, I was confusing usedrange with visiblerange, I think. Thanks for your patience :) – okapishomapi Sep 19 '12 at 16:16
+1, completely functional method (arguably better than mine). In ten iterations of 10000 times, your version seemed to out perform mine by an average of about .0001 seconds a realistically trivial amount. – Daniel Sep 19 '12 at 16:20
@DanielCook - Interesting. Probably because I limit my range to blank cells in the usedrange, where as you loop through each row to see if it's blank. So mine has less cells to loop through. You could improve your function, to only loop through the blank cells in the first column of the usedrange and it would be faster. To make it perfect, you'd have to account for the fact that a row may not have any blank cells, which potentially could happen. if that did, code would break due to the way VBA handles `SpecialCells`. – Scott Holtzman Sep 19 '12 at 16:24
@ScottHoltzman Amazing! I tried it out - it appears to work. The only thing I had to do was comment out line 5 (redundant declaration error), but that's probably because I'm on Excel 2003. Thank you again! – okapishomapi Sep 19 '12 at 17:02
@okapishomapi -> it was not an XL version error. I left that there by mistake. – Scott Holtzman Sep 19 '12 at 17:25
I converted this function to use Microsoft.Office.Interop.Excel in .Net and noticed that completely empty worksheets also exit at "Whoa! All rows in sheet are used. No blank rows exist!". – habakuk Mar 07 '17 at 10:52
Doesn't work if no unused rows in `UserRange` and badly composed post (the "original answer" part has negative usefulness). – ivan_pozdeev Jul 09 '17 at 05:13
This didn't work for me. I added a new sheet. Typed some data on, and ran this code against active sheet. It always gives me "Whoa!" message: MsgBox firstBlankRow(ThisWorkbook.ActiveSheet) – Heap of Pinto Beans May 11 '18 at 14:24

score 2 · Answer 4 · answered Mar 07 '14 at 17:31

I know this is an older thread however I needed to write a function that returned the first blank row WITHIN a range. All of the code I found online actually searches the entire row (even the cells outside of the range) for a blank row. Data in ranges outside the search range was triggering a used row. This seemed to me to be a simple solution:

Function FirstBlankRow(ByVal rngToSearch As Range) As Long
   Dim R As Range
   Dim C As Range
   Dim RowIsBlank As Boolean

   For Each R In rngToSearch.Rows
      RowIsBlank = True
      For Each C In R.Cells
         If IsEmpty(C.Value) = False Then RowIsBlank = False
      Next C
      If RowIsBlank Then
         FirstBlankRow = R.Row
         Exit For
      End If
   Next R
End Function

score 1 · Answer 5 · edited Oct 18 '13 at 19:41

1

ActiveSheet.Range("A10000").End(xlup).offset(1,0).Select

edited Oct 18 '13 at 19:41

SheetJS

22,470
12
65
75

answered Oct 18 '13 at 19:24

Josh Z

11
1

score 0 · Answer 6 · answered Jul 03 '14 at 15:43

very old thread but .. i was lookin for an "easier"... a smaller code

i honestly dont understand any of the answers above :D - 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

`intRow = intRow + 1` doesn't look like "bottom up". – ivan_pozdeev Jul 09 '17 at 05:17 — ivan_pozdeev, Jul 09 '17 at 05:17

score 0 · Answer 7 · answered Feb 03 '17 at 02:22

Very old thread but a simpler take :)

Sub firstBlank(c) 'as letter
    MsgBox (c & Split(Range(c & ":" & c).Find("", LookIn:=xlValues).address, "$")(2))
End Sub
Sub firstBlank(c) 'as number
    cLet = Split(Cells(1, c).address, "$")(1)
    MsgBox (cLet & Split(Range(cLet & ":" & cLet).Find("", LookIn:=xlValues).address, "$")(2))
End Sub

score 0 · Answer 8 · answered Feb 01 '18 at 22:15

Function firstBlankRow() As Long
Dim emptyCells As Boolean

    For Each rowinC In Sheet7.Range("A" & currentEmptyRow & ":A5000")   ' (row,col)

        If rowinC.Value = "" Then
            currentEmptyRow = rowinC.row
            'firstBlankRow = rowinC.row 'define class variable to simplify computing complexity for other functions i.e. no need to call function again
            Exit Function   
        End If
    Next

End Function

Finding first blank row, then writing to it

8 Answers8

Linked

Related