• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Hide by autofilter vba

Guys, please see the attached file.

It is an overtime calculation spreadsheet. Overtime criteria is row "F". When the hide button is clicked and if a cell in row "F" = 0 the autofilter hides those rows. The hide action is triggered if info in row "H">0.
I would like the code to go to the top visible cell in row "I" after the hide button is clicked.
For the attached scenario when the hide button is clicked the code should go to "I8", not "I5", the actual top open cell.

Thank you in advance for the direction with this code.

Robert
next-row.xlsm
0
rws1
Asked:
rws1
  • 3
  • 3
1 Solution
 
Anthony BerenguelCommented:
Hi Robert,

See the attached spreadsheet for an example of how to do this. Let me know if you have questions.

Regards,

AB
next-row.xlsm
0
 
rws1Author Commented:
AB, thank you for your response, your code is great from the module activation.

I have 7 simialar filters, do I need to copy; Public Sub Select_Top_Visible_Cell() for each action;
such as
Public Sub Select_Top_Visible_Cell()
Public Sub Select_Top_Visible_Cell1()
Public Sub Select_Top_Visible_Cell2()
And so forth or do you see a common denominator for all 7 filters?

Thank you again for your help,
Robert
0
 
Saqib Husain, SyedEngineerCommented:
You do not need a loop. Try

 Sheet1.AutoFilter.Range.Offset(, 1).SpecialCells(xlCellTypeVisible).Cells(1).Select
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
rws1Author Commented:
Thank you for your response, where would the code you sent be place in the editor?  

Sheet1.AutoFilter.Range.Offset(, 1).SpecialCells(xlCellTypeVisible).Cells(1).Select
0
 
Saqib Husain, SyedEngineerCommented:
In place of the

.... I5 ...select

line
0
 
Saqib Husain, SyedEngineerCommented:
Here is the file with the line inserted
Copy-of-next-row.xlsm
0
 
rws1Author Commented:
ssaqibh, thank you it works great
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now