Find first blank row and begin entering data

[camidon]

I'm trying to find a way to locate the first blank row and begin entering data. I've just started writing vba in Excel and I'm having trouble figuring out how to enter data into cells from vba. I have a form that has four text boxes on it and I need to be able to enter data into those text boxes and put them into cells in a worksheet.

I hope I'm asking this correctly.

Thanks in advance,

Chris

 

[rmikesmith]

Chris,

You can use something like the following:

NextBlankRow = Thisworkbook.Worksheets("SheetName").Cells(65536,KeyColumnNum).End(xlUp).Row + 1

NextBlankRow should be DIMensioned as Long
KeyColumnNum is the numeric representation of a column (A=1, B=2, etc.). The column you choose should be such that if a cell in this column is empty it signifies that the row is empty.

Hope this helps.
M. Smith

 

[ratman]

Consider this:

'----for testing 1st empty cell in column A----
If IsEmpty(Range("A2&quot.Value) Then
lastrow = 1
Else
lastrow = [A1].End(xlDown).Row

Thank you,
Dave Rattigan

 

[segmentationfault]

You could try this. Just point it to the column and the row where you want to start searching for blank rows as indicated in MinValidRow and ColWithBlanks. For example, to start looking for blanks in row 5 of column A, set these to 5 and 1 respectively.

Sub FindBlank()
    Dim MinValidRow As Long
    MinValidRow = 5
    Dim ColWithBlanks As Integer
    ColWithBlanks = 5
    Dim FirstRow As Long
    
    Columns(ColWithBlanks).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    For Each c In Selection
        If FirstRow = 0 And c.Row >= MinValidRow Then
            FirstRow = c.Row
            Cells(FirstRow, ColWithBlanks).Select
            Exit Sub
        End If
    Next c
End Sub

M. Smith's method will work of the data is contiguous, but since you're searching for blank rows, I thought perhaps that may not be the case. Dave Rattigan's solution works just as mine does, but I never skip an opportunity to be extra intricate.