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