Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Locate the first empty row in a worksheet

  Asked By: Lorraine    Date: Feb 09    Category: MS Office    Views: 1224
  

I have a similar problem: I need to put some data in the first empty row. For
example: in the range A1:D100 I need to fill the four columns in the first empty
row with data gathered by a form. So I need to know how to select (cell.select)
the A# cell, where # is the number of the first empty row.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Bama Cohen     Answered On: Feb 09

Sub FirstBlank()
select  the first line in your data
Range("C1").End(xlDown).Offset(1, 0).Select
End Sub

since you are feeding the rows from a form, you don't need to select the cell.
find the row  number of the first blank row

BlankRowNumber = Range("A1").End(xlDown).Offset(1, 0).Row

then load your data  from the form

Sub Form2Sheet()
RowNumber = Range("A1").End(xlDown).Offset(1, 0).Row 'find the first blank
row
If RowNumber < 100 Then ' ensure you are still in your range
ColNumber = 1 'Column A
With Me
.Controls("Your control Name").Value = Sheets("YOUR SHHET
NAME").Cells(RowNumber, ColNumber)
.Controls("Your control Name").Value = Sheets("YOUR SHHET
NAME").Cells(RowNumber, ColNumber + 1)
.Controls("Your control Name").Value = Sheets("YOUR SHHET
NAME").Cells(RowNumber, ColNumber + 2)
.Controls("Your control Name").Value = Sheets("YOUR SHHET
NAME").Cells(RowNumber, ColNumber + 3)
End With
End If
End Sub

 
Didn't find what you were looking for? Find more on Locate the first empty row in a worksheet Or get search suggestion and latest updates.




Tagged: