SendKeys command used in VBA toggles either my CapsLock and/or my NumLock on my keyboard.

[JohnZ1156]

I have a table. After I select a Filter from the column header, I would like to have the cell pointer go to either the first visible row in the table (CtrlHome) or scroll to the last visible row in the table (EndDown).
I created "found" 2 macros to do this and assigned the macros to buttons.

Sub CtrlHome()
    With ActiveWindow.ActivePane
        .ScrollRow = 1
        .ScrollColumn = 1
        .VisibleRange(1).Activate
    End With
    
    With Worksheets("Sheet1").AutoFilter.Range
       [B] .SpecialCells(xlCellTypeVisible).Areas(2)(1, 1).Select[/B]
    End With
End Sub

and

Sub EndDown()
    SendKeys ("^{Home}")
    SendKeys ("{End}{Down}")
End Sub

When I run the (CtrlHome) macro, it works find, but only if I have a Filter set. If I have not set a Filter, the second With (in bold) throws me to the debugger and that line is in yellow.
Question: Can this macro be modified to work whether or not there is a Filter set?

When I run the (EndDown) macro, it 'screws up" either my CapsLock and/or my NumLock toggle keys.
Question: Is there another way in VBA that I can move my cell pointer to scroll to the first visible row in column A or the last visible row in column A without using the dreaded SendKeys command?

 

[Micron]

Can this macro be modified to work whether or not there is a Filter set?

Maybe test if the filter is active or not?

If Worksheets("Sheet1").Autofiltermode
   Worksheets("Sheet1").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas(2)(1, 1).Select
End If

 

[Micron]

Is there another way in VBA that I can move my cell pointer to scroll to the first visible row in column A

Try

With Worksheets("Sheet1").AutoFilter.Range
    Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With

 

[JohnZ1156]

Hi Micron,
Thanks for your help.
Your second answer to my 1st question worked great! Now my CtrlHome button takes me to the first visible row in my table regardless if I have any filters active. Thanks again.
My 2nd question had to do with jumping to the last visible row in my table without using the SendKeys command.

I'm not going to click on the check box just yet because I have not received an answer to my 2nd question regarding SendKeys.
Question: Is there another way in VBA that I can move my cell pointer to scroll to the first visible row in column A or the last visible row in column A without using the dreaded SendKeys command?
I know I probably shouldn't have put 2 questions in one post, but originallyt started out as the same issue.

 

[Micron]

I'm confused. My 2nd answer was for your 2nd question. I tried to make that obvious by quoting the question with respect to each post.
If applied like that, you don't have 2 solutions? Or maybe you're asking for both first and last visible row options?

 

[JEC]

Try this

Sub jec()
Application.Goto Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Maybe for in the future while using sendkeys. If you use it like this, your capslock etc is not being messed up.

Sub EndDown()
CreateObject("wscript.shell").SendKeys "^{Home}{END}{DOWN}", True
End Sub

 

[JohnZ1156]

Thank you JEC.
Works great !!!

 

[JEC]

You're welcome!