Controlling Cursor position with Macro

bluegold

Active Member
Joined
Jun 21, 2009
Messages
279
Hi guys I'm hoping somebody can help me with this Macro code.

I want a macro that moves the cursor to the 1st empty row on column D or 1st empty row on column AP (the lowest row number should decide which column the cursor gets placed)
So if column D's 1st free row is lower than column AP than it should use Column D otherwise if Column AP's 1st free row is lower than column D than the cursor should go to column AP.

Is this clear?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You'll want to get the scroll portion adjusted, but see if this gets you started:

Code:
Sub foo()
    Dim lr1 As Long
    Dim lr2 As Long
    
    lr1 = Cells(Rows.Count, "D").End(xlUp).Row
    lr2 = Cells(Rows.Count, "AP").End(xlUp).Row
    
    If lr1 < lr2 Then
        Application.Goto Reference:=ActiveSheet.Range("D & lr1), Scroll:=True
    Else
        Application.Goto Reference:=ActiveSheet.Range("AP" & lr1), Scroll:=True
    End If
    
End Sub

HTH
 
Last edited:
Upvote 0
You'll want to get the scroll portion adjusted, but see if this gets you started:

Code:
Sub foo()
    Dim lr1 As Long
    Dim lr2 As Long
    
    lr1 = Cells(Rows.Count, "D").End(xlUp).Row
    lr2 = Cells(Rows.Count, "AP").End(xlUp).Row
    
    If lr1 < lr2 Then
        Application.Goto Reference:=ActiveSheet.Range("D & lr1), Scroll:=True
    Else
        Application.Goto Reference:=ActiveSheet.Range("AP" & lr1), Scroll:=True
    End If
    
End Sub

HTH

Thanks Smitty but I get a syntax error on this line

Application.Goto Reference:=ActiveSheet.Range("D & lr1), Scroll:=True

The line is even highlighted red before I start the macro like its missing something?
 
Upvote 0
Sorry about that, I wrote it by hand, and missed a closing quote after the "D. It should be:

Application.Goto Reference:=ActiveSheet.Range("D" & lr1), Scroll:=True
 
Upvote 0
Oops, I just noticed a goof in the Else statement:

Application.Goto Reference:=ActiveSheet.Range("AP" & lr1), Scroll:=True

It should be lr2, not lr1.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top