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

[shirley]

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

 

[grinning_crow]

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

 

[Bob Phillips]

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)

 

[Paul Robinson]

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

 

[Simon Prince]

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

 

[Bob Phillips]

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").End(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]

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

 

[shirley]

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").End(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

 

[shirley]

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

 

[Wouter]

Hi Sherley and others,

Try this for a change:

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

Good Luck

Wouter HM

 

[Bob Phillips]

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)