Find first empty row in a worksheet with a Macro

[JHalsall]

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

 

[Bob Phillips]

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)

 

[Guest]

Try Selection.CurrentRegion.Select

 

[Brian Taylor]

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.

 

[Brian Taylor]

Here are some good comments on this from Rodney Powell:

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