Search for the first blank line, then write to it

Question

I need to find the first empty line in the book and write the information in (line, 1) and (line, 2). I think I'm pretty stuck now ...

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.

Answer 1

If you mean the line number after the last line used, you can find it like this:

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

If you mean a line that after that becomes empty with data after it ... it becomes more complex.

A function is written here that will give you the actual line number of the first line, empty 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 submit any worksheet.

Edit: as Ooo pointed out, this will be an error if there are no empty cells in the used range.

Answer 2

I would do it like this. Briefly and clearly:)

 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 the first line is empty.

Edit: update to enable check if whole line 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 

Answer 3

Update

Inspired by the Daniel code above and the fact that this is the WAY! more interesting to me now, and then the actual work that I have to do, I created a robust full-featured function to find the first empty line on the sheet. Improvements are welcome! Otherwise, it will go to my library :) I hope others will benefit.

  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 space in the 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 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 

Answer 4

I know this is an old thread, but I need to write a function that returned the first empty line WITHIN of the range. All the code I found on the Internet actually scans the entire row (even cells out of range) for an empty row. Data in ranges outside the search range ran the used row. This seemed to me 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 

Answer 5

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

Answer 6

very old thread, but .. I was looking for "lighter" ... less code

I honestly don't understand any of the answers above: D - i'm a noob

but that should do the job. (for small sheets)

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

reads each cell in column 1 from bottom to top and stops in the first empty cell

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

then you can write your information as follows

 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 vba stream ... lol

Answer 7

A very old thread, but easier to 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 

Answer 8

FirstBlankRow () function for how 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 ie no need to call function again Exit Function End If Next 

Final function