Please help find the next empty cell

[pcarsquared]

Here is the scenerio any help would be great!!!!


1. So I have two pages in the worksheet one named "form" and the othe
named "data".
2. The form page has a submitt button at the bottom that runs a macr
that basically cuts and pastes all required data to "data page".

Easy enough...My Problem...

How do I find the next open row (on the "data" page) so when the secon
line of data is entered it does not overwrite the first? Is thi
something that can "easily" be done in excel?

Any thoughts of Excel wisdom would be great

 

[Tod]

This will tell you the first available row at the end of a
range.

NextRow = ActiveSheet.Range("A65536").End(xlup).Row

Or you could return the address.

NextRow = ActiveSheet.Range("A65536").End(xlup).Row

tod

 

[TJ Walls]

Private Function LastRow() As Integer
Dim i as Integer
Dim stp as Boolean

i = 1
stp = IsEmpty(Worksheets("data").Cells(i, 1))
While stp = False
i = i + 1
stp = IsEmpty(Worksheets("data").Cells(i, 1))
Wend

LastRow = i
End Function


Hope this helps.

-TJ

 

[Bob Phillips]

cNextRow = Worksheets("Data").Cells(Rows.Count,"A").End(xlUp).Row+1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[TJ Walls]

On Mon, 17 May 2004 15:25:54 -0500, wrote:


OR
lastRow = Worksheets("data").UsedRange.Rows.Count + 1

So many objects, so many members, so little time.

-TJ

 

[Anders S]

One way

'*****
Option Explicit

Sub saveForm()
Dim destCell As Range
Set destCell = Worksheets("Data").Cells(Rows.Count, "A") _
.End(xlUp).Offset(1, 0)

With destCell
'assuming Form is the active sheet
.Offset(0, 0).Value = Range("B1").Value
.Offset(0, 1).Value = Range("B2").Value
.Offset(0, 2).Value = Range("B3").Value
.Offset(0, 3).Value = Range("B4").Value
End With

End Sub
'*****

HTH
Anders Silven