Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to blank row on bew sheet | Setting up and Configuration of Excel | |||
Macro ?-moving info from 1 sheet to first blank section on another | Excel Discussion (Misc queries) | |||
blank sheet | Excel Discussion (Misc queries) | |||
Macro not recognizing blank lines as blank | Excel Discussion (Misc queries) | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |