how to direct users to the empty row below the last record in excel
 

I need help. i want to direct users to the empty row just below the las
record in excel. what is the coding

--
Message posted from http://www.ExcelForum.com

how to direct users to the empty row below the last record in excel
 

something like this... assuming your data includes A1



sub NextRow()

sheet1.rows(sheet1.range("A1").currentregion.rows. count+1).select

end sub



Either attach it to a button or include it in the openworkboo
procedure in the workbook.

Hope this is of some help

--
Message posted from http://www.ExcelForum.com

how to direct users to the empty row below the last record in excel
 

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

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

"shirley " wrote in message
...
I need help. i want to direct users to the empty row just below the last
record in excel. what is the coding?


---
Message posted from http://www.ExcelForum.com/

how to direct users to the empty row below the last record in excel
 

Hi,
Could you say what direct means and when it should happen?
regards
Paul

shirley wrote in message ...
I need help. i want to direct users to the empty row just below the last
record in excel. what is the coding?


---
Message posted from http://www.ExcelForum.com/

how to direct users to the empty row below the last record in excel
 

The following function is something that I've amended from
Walkenbach's book (I think). It gives the last used row in a
particular column. As I recollect, Walkenbach's code is more compact
(and works perfectly of course) but is harder to understand.

So to run the following function, you'd maybe put something like:
dim mysheet as worksheet
set mysheet=activeworkbook.worksheets("Sheet1")
mysheet.cells(ColumnLast(mysheet,7),7).activate
.....



Function ColumnLast(inputsheet as worksheet,colnum as integer) as
integer
Dim rng1 as range,rng2 as range, rng3 as range
Dim collen as integer, i as integer

set rng1 = inputsheet.columns(colnum)
set rng2 = inputsheet.usedrange
set rng3 = intersect(rng1,rng2)
collen=rng3.count

for i=collen to 1 step -1
if not IsEmpty(rng3(i)) then
ColumnLast = i+1
Exit Function
next i

columnlast=1

end function



shirley wrote in message ...
I need help. i want to direct users to the empty row just below the last
record in excel. what is the coding?


---
Message posted from http://www.ExcelForum.com/

how to direct users to the empty row below the last record in excel
 

Simon,

Apart from the fact that this has a missing End IF which gives a compile
error and that it gives the last+1 rather than last, it seems very
complicated and inefficient compared to this tried and trusted method,
adapted to your example


mySheet.Cells(activesheet.Cells(Rows.Count,"G").En d(xlUp).Row,7).Activate

--

HTH

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

"Simon Prince" wrote in message
om...
The following function is something that I've amended from
Walkenbach's book (I think). It gives the last used row in a
particular column. As I recollect, Walkenbach's code is more compact
(and works perfectly of course) but is harder to understand.

So to run the following function, you'd maybe put something like:
dim mysheet as worksheet
set mysheet=activeworkbook.worksheets("Sheet1")
mysheet.cells(ColumnLast(mysheet,7),7).activate
....



Function ColumnLast(inputsheet as worksheet,colnum as integer) as
integer
Dim rng1 as range,rng2 as range, rng3 as range
Dim collen as integer, i as integer

set rng1 = inputsheet.columns(colnum)
set rng2 = inputsheet.usedrange
set rng3 = intersect(rng1,rng2)
collen=rng3.count

for i=collen to 1 step -1
if not IsEmpty(rng3(i)) then
ColumnLast = i+1
Exit Function
next i

columnlast=1

end function



shirley wrote in message
...
I need help. i want to direct users to the empty row just below the last
record in excel. what is the coding?


---
Message posted from http://www.ExcelForum.com/

how to direct users to the empty row below the last record in excel
 

Bob,
Apologies for the missing end if, I was copying from laptop to
desktop. I stand in awe of the simplicity of your approach. Many
thanks. Always learning.

Simon

"Bob Phillips" wrote in message ...
Simon,

Apart from the fact that this has a missing End IF which gives a compile
error and that it gives the last+1 rather than last, it seems very
complicated and inefficient compared to this tried and trusted method,
adapted to your example


mySheet.Cells(activesheet.Cells(Rows.Count,"G").En d(xlUp).Row,7).Activate

--

HTH

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

"Simon Prince" wrote in message
om...
The following function is something that I've amended from
Walkenbach's book (I think). It gives the last used row in a
particular column. As I recollect, Walkenbach's code is more compact
(and works perfectly of course) but is harder to understand.

So to run the following function, you'd maybe put something like:
dim mysheet as worksheet
set mysheet=activeworkbook.worksheets("Sheet1")
mysheet.cells(ColumnLast(mysheet,7),7).activate
....



Function ColumnLast(inputsheet as worksheet,colnum as integer) as
integer
Dim rng1 as range,rng2 as range, rng3 as range
Dim collen as integer, i as integer

set rng1 = inputsheet.columns(colnum)
set rng2 = inputsheet.usedrange
set rng3 = intersect(rng1,rng2)
collen=rng3.count

for i=collen to 1 step -1
if not IsEmpty(rng3(i)) then
ColumnLast = i+1
Exit Function
next i

columnlast=1

end function



shirley wrote in message
...
I need help. i want to direct users to the empty row just below the last
record in excel. what is the coding?


---
Message posted from http://www.ExcelForum.com/

how to direct users to the empty row below the last record in excel
 

Hi to all,

thanks for the help.
when i added in the code

dim mysheet as worksheet
set mysheet=activeworkbook.worksheets("Sheet6")

mySheet.Cells(activesheet.Cells(Rows.Count,"A").En d(xlUp).Row,7).Activate

i received an error = "subscript out of range"

what is wrong? btw, does the code mean to count through all rows, g
to the last record which is the last row? what does 7 means

--
Message posted from http://www.ExcelForum.com

how to direct users to the empty row below the last record in excel
 

Hi to all again,

i think i got the code. i made some changes to accomodate my progra
and solve the out of subscript problem.

Dim mysheet As Worksheet
Set mysheet = ActiveSheet
mysheet.Row.Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
1).Next.Activate


However, this code brings me to the last record. How do i change th
code to bring me to the empty row jus under the last row it found

--
Message posted from http://www.ExcelForum.com

how to direct users to the empty row below the last record in excel
 

Hi Sherley and others,

Try this for a change:

With ActiveSheet
.Cells(.UsedRange.Rows.Count + 1, 1).Select
End With

Good Luck

Wouter HM

shirley wrote in message ...
Hi to all again,

i think i got the code. i made some changes to accomodate my program
and solve the out of subscript problem.

Dim mysheet As Worksheet
Set mysheet = ActiveSheet
mysheet.Row.Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row,
1).Next.Activate


However, this code brings me to the last record. How do i change the
code to bring me to the empty row jus under the last row it found?


---
Message posted from http://www.ExcelForum.com/

how to direct users to the empty row below the last record in excel
 

Shirley,

Just add 1

Dim mysheet As Worksheet
Set mysheet = ActiveSheet
mysheet.Row.Cells(ActiveSheet.Cells(Rows.Count,
"A").End(xlUp).Row+1,1).Next.Activate

--

HTH

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

"shirley " wrote in message
...
Hi to all again,

i think i got the code. i made some changes to accomodate my program
and solve the out of subscript problem.

Dim mysheet As Worksheet
Set mysheet = ActiveSheet
mysheet.Row.Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row,
1).Next.Activate


However, this code brings me to the last record. How do i change the
code to bring me to the empty row jus under the last row it found?


---
Message posted from http://www.ExcelForum.com/