Excel VBA, find the next empty cell

Question

What I am trying to do should be easy but the noob in me is showing. I am creating a database in excel using a dataentry form in another worksheet. When the “enter” button is clicked, it runs a macro that places the info from the dataentry sheet to an Excel database. The problem is, of course, that the next record overwrites the previous record. What I want to do is copy the data from a field in the dataentry form, then go to the database and find the next empty row and paste the info there. Ideally, the procedure would continue and copy the next field in the dataentry form, find the empty cell to the right of the data previously pasted and then repeat until all 6 fields have been copied and pasted to the excel database. I want to do this all in excel rather than into an access database. Any ideas?

Welcome to SO! There are numerous examples of how to do this on the web. Please [see here](http://stackoverflow.com/help/on-topic) for how to ask questions on SO, and [here](http://stackoverflow.com/help/how-to-ask) for what makes a good question. — ARich, Dec 21 '15 at 03:11
Possible duplicate of [Select first empty cell in column F starting from row 1. (without using offset )](http://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset) — ARich, Dec 21 '15 at 03:13

score 2 · Answer 1 · answered Dec 20 '15 at 17:20

You could try

Sub LastRow() 
    Dim rngLast As Range 

    With ThisWorkbook.Worksheets("Sheet1") 
        If .Range("A1").Value = "" Then 
            Set rngLast = .Range("A1") 
        Else 
            Set rngLast = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) 
        End If 
        MsgBox "Next blank row is " & rngLast.Address 
    End With 

End Sub

score 1 · Accepted Answer · answered Dec 20 '15 at 20:18

This will find the very last cell (last column & last row) used in the workbook & sheet provided. Adapt to taste (e.g you don't need targetwkb if you only work with one workbook). In case you don't need to find the last column, or just want the specific last row of a given column, you could just define different the "With" part (w.e. "With Worksheets(targetSheet).Range("A:A") or something similar:

Function FindLastCell(targetWbk As String, targetSheet As String) As Range
Dim LastColumn As Integer, lastRow As Integer
'Finds the last used cell in target wbk/sheet

With Workbooks(targetWbk).Worksheets(targetSheet)
   lastRow = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,       SearchDirection:=xlPrevious).row
   LastColumn = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
   Set FindLastCell = Range(.Cells(1, 1), .Cells(lastRow, LastColumn))
End With
End Function

The above is a better approach overall because it searches from the last cell to the top. The otherway around, if you have an empty cell then Excel will stop searching, and you could overwrite all the data that follows just because a blank cell was left somewhere.

If you have lots of data (like 10s of thousands) you may want to change integer for long, so you don't bump on the integer limit. It also doesn't need to be a function, could be a sub but doesn't matter much...

Excel VBA, find the next empty cell

2 Answers2