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

JohnZ1156

Board Regular
Joined
Apr 10, 2021
Messages
180
Office Version
  1. 2021
Platform
  1. Windows
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.

VBA Code:
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

VBA Code:
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Can this macro be modified to work whether or not there is a Filter set?
Maybe test if the filter is active or not?

VBA Code:
If Worksheets("Sheet1").Autofiltermode
   Worksheets("Sheet1").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas(2)(1, 1).Select
End If
 
Upvote 0
Is there another way in VBA that I can move my cell pointer to scroll to the first visible row in column A
Try
VBA Code:
With Worksheets("Sheet1").AutoFilter.Range
    Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Try this

VBA Code:
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.

VBA Code:
Sub EndDown()
CreateObject("wscript.shell").SendKeys "^{Home}{END}{DOWN}", True
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

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