Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 5
Default 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



  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 35,218
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 11,123
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to blank row on bew sheet Bigpond Setting up and Configuration of Excel 4 September 17th 08 09:41 PM
Macro ?-moving info from 1 sheet to first blank section on another carrera Excel Discussion (Misc queries) 6 March 12th 08 09:19 PM
blank sheet Rider Excel Discussion (Misc queries) 20 July 19th 07 02:18 PM
Macro not recognizing blank lines as blank pm Excel Discussion (Misc queries) 9 May 22nd 07 04:16 PM
2 questions, copying data from sheet to sheet and assigning macro Boris Excel Worksheet Functions 0 December 16th 04 07:11 PM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"