Find first empty row in a worksheet with a Macro
 

I have a macro that takes data from one sheet and adds it into a table
on another form, at the moment it all works fine as long as all fields
are filled in everytime a new entry is added. What I want is to be
able to have some fields blank, to do this I need to be able to find
the last row with data in any column on the first sheet, and the first
row with no data in any columns on the next sheet. Is there a simple
way this can be done.
At the moment I use this:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

but this will find the first empty fields and so doesn't necesarily
find all the data.

Thanks for any assistance.

J


--
JHalsall
------------------------------------------------------------------------
JHalsall's Profile: http://www.excelforum.com/member.php...o&userid=34382
View this thread: http://www.excelforum.com/showthread...hreadid=541627

Find first empty row in a worksheet with a Macro
 

Using your approach,

Cells(1, Columns.Count).End(xlToLeft).Select
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select

although this is better

Dim LastRow As Long
Dim LastCol As Long
Dim LastCell As Range
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set LastCell = Cells(LastRow, LastCol)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"JHalsall" wrote in
message ...

I have a macro that takes data from one sheet and adds it into a table
on another form, at the moment it all works fine as long as all fields
are filled in everytime a new entry is added. What I want is to be
able to have some fields blank, to do this I need to be able to find
the last row with data in any column on the first sheet, and the first
row with no data in any columns on the next sheet. Is there a simple
way this can be done.
At the moment I use this:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

but this will find the first empty fields and so doesn't necesarily
find all the data.

Thanks for any assistance.

J


--
JHalsall
------------------------------------------------------------------------
JHalsall's Profile:
http://www.excelforum.com/member.php...o&userid=34382
View this thread: http://www.excelforum.com/showthread...hreadid=541627

Find first empty row in a worksheet with a Macro
 

Try Selection.CurrentRegion.Select
--
- K Dales


"JHalsall" wrote:


I have a macro that takes data from one sheet and adds it into a table
on another form, at the moment it all works fine as long as all fields
are filled in everytime a new entry is added. What I want is to be
able to have some fields blank, to do this I need to be able to find
the last row with data in any column on the first sheet, and the first
row with no data in any columns on the next sheet. Is there a simple
way this can be done.
At the moment I use this:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

but this will find the first empty fields and so doesn't necesarily
find all the data.

Thanks for any assistance.

J


--
JHalsall
------------------------------------------------------------------------
JHalsall's Profile: http://www.excelforum.com/member.php...o&userid=34382
View this thread: http://www.excelforum.com/showthread...hreadid=541627

Find first empty row in a worksheet with a Macro
 

I use this to find the last real used row:

Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:= xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

The same can be used to return column or address.

Find first empty row in a worksheet with a Macro
 

Here are some good comments on this from Rodney Powell:

http://www.beyondtechnology.com/geeks012.shtml