I have found and adapted a piece of code which works extremely well however there are a couple of bits I need to change and
wondered if anyone could help.
I have a 'Department Codes' sheet which the user can select departments the want by
putting a 'Y' in column E.
In the main sheet (Reviews) where the VBA code is, I use the following formula
=INDEX('Department Codes'!E:E,MATCH(Reviews!B86,'Department Codes'!B:B,1))
This brings in all the 'Y' that match into column A.
The VBA code I use keeps all the Y rows visible and hides all other rows.
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("a6:a454")
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cell In r
If cell.Value = Y Then
cell.EntireRow.Hidden = True
cell.EntireRow.Hidden = False
Application.ScreenUpdating = True
Application.EnableEvents = True
I need to speed the process up, so is it possible to make the Code only update when I make a change to the 'Department Codes'
sheet rather than updating after every change in the whole workbook?
Also the VBA code now doesn’t allow me to Undo/Redo any changes I make, is there an additional piece of code to allow me to
use the function again?
And lastly, I want to protect the 'Review' sheet from changes being made however when it is protected the VBA code doesn’t
hide/unhide the rows, is there code I can use to override this?
Any help would be greatly appreciated.