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

  • Thread starter Thread starter shirley
  • Start date Start date
S

shirley

I need help. i want to direct users to the empty row just below the las
record in excel. what is the coding
 
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
 
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)
 
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
 
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)
 
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
 
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
 
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
 
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,

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)
 
Back
Top