Finding first blank row, then writing to it

by

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.

More Related Contents:

  1. Somehow it’s not copying [closed]
  2. VBA macro that search for file in multiple subfolders
  3. So, I have 6 “master” files to then divide into 40 separate files
  4. Getting ScriptControl to work with Excel 2010 x64
  5. Application.Match gives type mismatch
  6. Count and Highlight keywords within phrases
  7. Transpose a range in VBA
  8. Protecting cells in Excel but allow these to be modified by VBA script
  9. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  10. Detect merged cells in VBA Excel with MergeArea
  11. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  12. Parse HTML content in VBA
  13. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  14. How do I name a spreadsheet automatically, by referencing two cells?
  15. Excel VBA Delete Rows
  16. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  17. Should I turn .CutCopyMode back on before exiting my sub procedure?
  18. VBA: How to delete filtered rows in Excel?
  19. Determine whether user is adding or deleting rows
  20. How to use VBA SaveAs without closing calling workbook?
  21. Removing the password from a VBA project
  22. Removing special characters VBA Excel
  23. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  24. Iterate over slicer via VBA and select a single item each time
  25. Loop through Excel Sheets
  26. How can I open this VBA workbook faster?
  27. How can I automate Save as dialog box in IE11 using VBA?
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment