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    amp  Cell  amp    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

User · Accepted Answer

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.

User · Answer

Function firstBlankRow   As Long     Dim emptyCells As Boolean      For Each rowinC In Sheet7 Range  A   amp  currentEmptyRow  amp    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ActiveSheet Range  A10000   End xlup  offset 1 0  Select

User · Answer

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      - gt  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  - gt  no blanks in first column of used range              If Application WorksheetFunction CountA Cells  Rows Count  1  EntireRow    0 Then  - gt  is the last row of the sheet blank                    - gt  yeap   then no blank rows                  MsgBox  Whoa  All rows in sheet are used  No blank rows exist                 Else                   - gt  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    amp  Cell  amp    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  - gt  make sure blank cell exists in first column of usedrange          - gt  find all blank rows in column A within the used range         Set rngBlanks   Intersect  UsedRange   Columns 1   SpecialCells xlCellTypeBlanks           For Each cel In rngBlanks  - gt  loop through blanks in column A               - gt  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

User · Answer

Very old thread but a simpler take     Sub firstBlank c   as letter     MsgBox  c  amp  Split Range c  amp       amp  c  Find     LookIn  xlValues  address       2   End Sub Sub firstBlank c   as number     cLet   Split Cells 1  c  address       1      MsgBox  cLet  amp  Split Range cLet  amp       amp  cLet  Find     LookIn  xlValues  address       2   End Sub

[vba] Finding first blank row, then writing to it

Examples related to vba

Examples related to excel