Go to first blank record

[Egg 'n' Bacon]

Struggling to finish this - got some code to take the user to the first blank row of the input sheet, but just can't get it :(

Here's the problem code;

Code: Select all

Sub Go2Input()

    Dim wsht As Worksheet
    Dim r As Long

    Set wsht = Worksheets("Input")
    r = wsht.Cells.Find(What:="*", searchorder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1

    wsht.Range("B" & r).Select

End Sub

[Rudi]

You can try this...

Code: Select all

Sub Go2Input()
Dim wsht As Worksheet
    Set wsht = Worksheets("Input")
    wsht.Cells(Rows.Count, 2).End(xlUp).Offset(1).Select
End Sub

...or if its uneven column lengths try...

Code: Select all

Sub Go2Input()
Dim wsht As Worksheet
Dim r As Long
    Set wsht = Worksheets("Input")
    r = wsht.UsedRange.Rows.Count + 1
    wsht.Cells(r, 2).Select
End Sub

[HansV]

This code will select the cell in column B in the first completely unused row of the worksheet. This is not necessarily the first unused cell in column B.

Selecting a cell will only succeed if the worksheet is active, so if you run this code from another sheet, you should insert a line

Code: Select all

    wsht.Select

just above the line wsht.Range("B" & r).Select. Alternatively, replace wsht.Range("B" & r).Select with

Code: Select all

    Application.GoTo wsht.Range("B" & r), True

[Egg 'n' Bacon]

Cheers Hans.
Works well, except that I hadn't taken into account the formulae in columns S & T.
Is it possible to restrict the row(?) count to just column B?

This is how it is at the moment:

Code: Select all

Sub Go2Input()
Dim wsht As Worksheet
Dim r As Long
    Set wsht = Worksheets("Input")
    r = wsht.UsedRange.Rows.Count + 1
    wsht.Select
    wsht.Cells(r, 2).Select
End Sub

[HansV]

You could use the first version posted by Rudi - this looks at column B only.

[Egg 'n' Bacon]

Get error 1004; Select method of Range class failed on this line:
wsht.Cells(Rows.Count, 2).End(xlUp).Offset(1).Select

[HansV]

Try this:

Application.GoTo wsht.Cells(wsht.Rows.Count, 2).End(xlUp).Offset(1)

[Egg 'n' Bacon]

Yay!

Thank you :)