Finding the first empty row with VBA

[Melker]

Hi everyone,

I am bulding an "data enter" userform. In this userform i am entering data
that should then be sent in to a excel based "database" when i press OK. The
data is oriented in colums. What i now have problems with is to get VBA to
search for the first empty row in i specific colum.

I guess this is quite simple but i am just starting to learn VBA.

Regards and thanx

Melker

 

[Paul B]

Melker, this will select the next available cell in column A, is this what
you want?

Range("A65536").End(xlUp).Offset(1, 0).Select
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[Melker]

Hi Paul,

That seemed to work perfectly.

Thank you !

Melker

 

[Guest]

Paul and Melker,
The listed solution only works on the active worksheet. if the "database" is
a hidden sheet, it must be identified. It also can not be "selected". This
works every time...

Function lngNextRow() As Long
Dim wksData as Worsheet
On Error Resume Next

Set wksData = Workbooks(??).Worksheets(??)
lngNextRow = wksData.cells(wksData.Rows.Count, 1).End(xlUP).Row + 1
Exit Function

Obviously, you must swap the ?? with real names or indexes.
This code also assumes that Column "A" of each record will be populated with
data. If not, create a horizontal loop through the columns.
Last, the function returns a long integer. Excel has more rows than what
will fit into a standard integer. I have had many a program blow up after
Excel went from 16,384 rows to 65,536


Dale

 

[Tom Ogilvy]

If you are going to make it a function, why not pass in the necessary
information

Function lngNextRow(bkName as String, shName as String, iCol as Long) As
Long
Dim wksData as Worsheet
On Error Resume Next
Set wksData = Workbooks(bkName).Worksheets(shName)
if wksData is nothing then
lngNextRow = 0
exit function
End if
lngNextRow = wksData.cells(wksData.Rows.Count, iCol).End(xlUP).Row + 1
On Error goto 0
Exit Function


then call it

lRow = lngNextRow("Mybook.xls","Sheet1", 3)
if lRow = 0 then
msgbox "Bad data"
exit sub
End if