Finding the first empty row with VBA
 

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

Finding the first empty row with VBA
 

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" wrote in message
...
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

Finding the first empty row with VBA
 

Hi Paul,

That seemed to work perfectly.

Thank you !

Melker
"Paul B" skrev i meddelandet
...
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" wrote in message
...
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

Finding the first empty row with VBA
 

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


"Melker" wrote:

Hi Paul,

That seemed to work perfectly.

Thank you !

Melker
"Paul B" skrev i meddelandet
...
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" wrote in message
...
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

Finding the first empty row with VBA
 

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

--
Regards,
Tom Ogilvy


"Dale Preuss" <Dale wrote in message
...
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


"Melker" wrote:

Hi Paul,

That seemed to work perfectly.

Thank you !

Melker
"Paul B" skrev i meddelandet
...
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" wrote in message
...
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