Macro to take me to a blank row on a new sheet
 

Thanks Roger but it is not working, I want it to go to the first vacant
empty row in column B
My Macro looks like this
Sub NEWOUT()
'
' NEWOUT Macro
'

'
Sheets("Loan Area").Select
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

End Sub

It still takes me to B2 each time vacant or full

Any suggestions for an old amature

thanks
"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

Dim Lr as long
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

lr will give the row number of the first empty row in column A

--
Regards
Roger Govier

"Bigpond" wrote in message
...
Can you help please
I am writing a data base in excel for our school, to track the resources,
I
need to have a macro that will take person from the Index Spread Sheet to
a
Spreadsheet called "Loan Area" and then take them to the next available
(empty) row on that sheet so that they can fill in their details,
I just cant get it to take them to the first blank cell in column A

Thanks

Macro to take me to a blank row on a new sheet
 

If you want to use column B, try changing the code to use column B, too:

Sub NEWOUT()
Dim Lr As Long
with Sheets("Loan Area")
.Select
Lr = .Cells(.Rows.Count, "b").End(xlUp).Row + 1
.cells(lr,"B").select
end with
End Sub

This will actually take to the next available cell after the last used cell in
column B.

Bigpond wrote:

Thanks Roger but it is not working, I want it to go to the first vacant
empty row in column B
My Macro looks like this
Sub NEWOUT()
'
' NEWOUT Macro
'

'
Sheets("Loan Area").Select
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

End Sub

It still takes me to B2 each time vacant or full

Any suggestions for an old amature

thanks
"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

Dim Lr as long
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

lr will give the row number of the first empty row in column A

--
Regards
Roger Govier

"Bigpond" wrote in message
...
Can you help please
I am writing a data base in excel for our school, to track the resources,
I
need to have a macro that will take person from the Index Spread Sheet to
a
Spreadsheet called "Loan Area" and then take them to the next available
(empty) row on that sheet so that they can fill in their details,
I just cant get it to take them to the first blank cell in column A

Thanks


--

Dave Peterson

Macro to take me to a blank row on a new sheet
 

I also reply to a private mail from the OP

Test this one

Sub LastRow_Example()
Dim LastRow As Long
Dim rng As Range

' Use all cells on the sheet
Set rng = Sheets("Loan Area").Cells

' Find the last row
LastRow = Last(1, rng)

Application.Goto rng.Parent.Cells(LastRow + 1, 2)

End Sub

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Long

Select Case choice

Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

On Error Resume Next
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

On Error Resume Next
Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0

End Select
End Function



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dave Peterson" wrote in message ...
If you want to use column B, try changing the code to use column B, too:

Sub NEWOUT()
Dim Lr As Long
with Sheets("Loan Area")
.Select
Lr = .Cells(.Rows.Count, "b").End(xlUp).Row + 1
.cells(lr,"B").select
end with
End Sub

This will actually take to the next available cell after the last used cell in
column B.

Bigpond wrote:

Thanks Roger but it is not working, I want it to go to the first vacant
empty row in column B
My Macro looks like this
Sub NEWOUT()
'
' NEWOUT Macro
'

'
Sheets("Loan Area").Select
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

End Sub

It still takes me to B2 each time vacant or full

Any suggestions for an old amature

thanks
"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

Dim Lr as long
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

lr will give the row number of the first empty row in column A

--
Regards
Roger Govier

"Bigpond" wrote in message
...
Can you help please
I am writing a data base in excel for our school, to track the resources,
I
need to have a macro that will take person from the Index Spread Sheet to
a
Spreadsheet called "Loan Area" and then take them to the next available
(empty) row on that sheet so that they can fill in their details,
I just cant get it to take them to the first blank cell in column A

Thanks


--

Dave Peterson