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

  • Thread starter Thread starter Bigpond
  • Start date Start date
B

Bigpond

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