How to display all hidden rows and columns

[AyaSofea]

Hi,
Basically, I have a data about 86 lines of rows in my worksheet. So there is the columns: A,B,C,D,E,F,G,H,J,K,L,M,N,O.

My range that I want to be filtered is from A7:O94.

But when I click button filter in the worksheet, the header name of each column from A:0 does not appear.

Example data:

Capture.jpg

I also have created a filter user form in my worksheet, and I'm just used a 3 column (range from B) to create a filter function, as the example below:

filterForm.jpg


So there is the example VBA code for FilterCommandButton:

Private Sub FilterCommandButton_Click()
Call CancelCommandButton_Click
If OptionButton1.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=EquipComboBox.Value, Operator:=xlAnd
End If
If OptionButton2.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=TaskComboBox.Value, Operator:=xlAnd
End If
If OptionButton3.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:=SizeComboBox.Value, Operator:=xlAnd
End If
End Sub

There is the code for Call CancelCommandButton:

Private Sub CancelCommandButton_Click()
Range("A7:P777").AutoFilter
    Range("A9").Select
End Sub

Thanks!

[Logit]

.
This short macro will unhide all columns and rows :

Sub UnhideAll()
    Columns.EntireColumn.Hidden = False
    Rows.EntireRow.Hidden = False
End Sub

[AyaSofea]

Thanks, for the respond.
I already add this code, but it's still not working.

[Logit]

.
That is a separate macro by itself. You would need to attach a Command Button to the macro for it to work.

Or ......

Include only these two lines where appropriate :

Columns.EntireColumn.Hidden = False 
Rows.EntireRow.Hidden = False

[AyaSofea]

Sorry, it's that I have to include these code in my worksheet or modules?

Because when I included these code, it's work but sometimes not.

[Logit]

.
I am confused with your code. Not certain how it flows without actually seeing the workbook and code here.

You could try placing those two lines of code in this macro :

Private Sub CancelCommandButton_Click()

Columns.EntireColumn.Hidden = False 
Rows.EntireRow.Hidden = False


Range("A7:P777").AutoFilter
Range("A9").Select


End Sub

[AyaSofea]

hmm. Sorry, so here I upload the attachment of my workbook.

IndexNorm.xlsm

[Logit]

.
Does this do the job ?

Private Sub CancelCommandButton_Click()


    Application.ScreenUpdating = False
    Columns.EntireColumn.Hidden = False
    Rows.EntireRow.Hidden = False


    Range("A7:P777").AutoFilter
    Range("A7:P777").AutoFilter
    Range("A9").Select
    Application.ScreenUpdating = True
    
End Sub


Private Sub ClearCommandButton_Click()
Application.ScreenUpdating = False
EquipComboBox = Empty: TaskComboBox = Empty: SizeComboBox = Empty
OptionButton1.Value = False: OptionButton2.Value = False: OptionButton3.Value = False
Application.ScreenUpdating = True
End Sub
Private Sub EquipComboBox_Change()


Dim index As Integer
index = EquipComboBox.ListIndex
Application.ScreenUpdating = False
TaskComboBox.Clear


Select Case index
    Case Is = 0
        With TaskComboBox
            .AddItem "To perform control valve overhaul"
            .AddItem "To perform control valve hydrotest"
            .AddItem "To perform control valve seat leakage test"
            .AddItem "To replace control valve positioner"
            .AddItem "To perform control valve site calibration / functional test (digital)"
            .AddItem "To perform control valve site calibration / functional test (analog)"
            .AddItem "To perform control valve site calibration / functional test (electro-pneumatic)"
            .AddItem "To replace control valve accessories"
        End With
    Case Is = 1
        With TaskComboBox
            .AddItem "To perform control valve overhaul"
            .AddItem "To perform control valve hydrotest"
            .AddItem "To perform control valve seat leakage test"
        End With
    Case Is = 2
        With TaskComboBox
            .AddItem "To perform Pressure/DP/Temperature transmitter replacement"
            .AddItem "To perform Pressure/DP/Temperature transmitter online diagnostic"
        End With
    Case Is = 3
        With TaskComboBox
            .AddItem "To perform transmitter site functional/calibration test"
        End With
    Case Is = 4
        With TaskComboBox
            .AddItem "To perform transmitter site functional/calibration test"
        End With
    Case Is = 5
        With TaskComboBox
            .AddItem "To perform Thermocouple/RTD sensor verification"
            .AddItem "To perform Thermocouple/RTD sensor replacement"
        End With
    Case Is = 6
        With TaskComboBox
            .AddItem "To perform transmitter site functional/calibration test"
        End With
    Case Is = 7
        With TaskComboBox
            .AddItem "To perform pressure/DP switch calibration/functional test"
        End With
    Case Is = 8
        With TaskComboBox
            .AddItem "To perform temperature switch calibration/functional test"
        End With
    Case Is = 9
        With TaskComboBox
            .AddItem "To perform temperature switch calibration/functional test"
        End With
    Case Is = 10
        With TaskComboBox
            .AddItem "Orifice plate inspection"
        End With
    Case Is = 11
        With TaskComboBox
            .AddItem "To perform function test"
        End With
    Case Is = 12
        With TaskComboBox
            .AddItem "To perform gas detector site functional test / calibration"
            .AddItem "To perform gas detector sensor replacement"
            .AddItem "To perform gas detector controller card replacement"
        End With
    Case Is = 13
       With TaskComboBox
            .AddItem "To perform smoke detector site functional test / calibration"
            .AddItem "To perform replacement"
        End With
    Case Is = 14
        With TaskComboBox
            .AddItem "To perform functional test"
            .AddItem "To perform replacement"
        End With
    Case Is = 15
        With TaskComboBox
            .AddItem "To perform function test"
            .AddItem "To perform replacement"
        End With
    Case Is = 16
        With TaskComboBox
            .AddItem "Replace faulty module"
        End With
    Case Is = 17
        With TaskComboBox
            .AddItem "Preventive Maintanance"
        End With
    Case Is = 18
        With TaskComboBox
            .AddItem "To perform function test"
            .AddItem "To perform replacement"
        End With
    Case Is = 19
        With TaskComboBox
            .AddItem "To perform turbine meter online calibration"
            .AddItem "To perform turbine meter offline calibration"
        End With
    Case Is = 20
        With TaskComboBox
            .AddItem "To perform pipe prover calibration"
        End With
    Case Is = 21
        With TaskComboBox
            .AddItem "Flowcomputer integration check"
        End With
    Case Is = 22
        With TaskComboBox
            .AddItem "Intrument Validation activity"
        End With
    Case Is = 23
        With TaskComboBox
            .AddItem "Intrument Validation activity"
        End With
    Case Is = 24
        With TaskComboBox
            .AddItem "To perform CCTV system functionality"
            .AddItem "To perform hardware camera check"
            .AddItem "To perform CCTV replacement"
            .AddItem "To perform recoder card replacement"
        End With
    Case Is = 25
        With TaskComboBox
            .AddItem "Perform calibration"
            .AddItem "Replace sensor"
            .AddItem "Replace Transmitter"
        End With
    Case Is = 26
        With TaskComboBox
            .AddItem "Perform calibration"
            .AddItem "Replace sensor"
            .AddItem "Replace Transmitter"
        End With
    Case Is = 27
        With TaskComboBox
            .AddItem "Perform calibration"
        End With
    Case Is = 28
        With TaskComboBox
            .AddItem "Perform calibration"
        End With
    Case Is = 29
        With TaskComboBox
            .AddItem "Perform calibration"
        End With
    Case Is = 30
        With TaskComboBox
            .AddItem "Perform calibration"
        End With
    Case Is = 31
        With TaskComboBox
            .AddItem "Perform calibration"
        End With
    Case Is = 32
        With TaskComboBox
            .AddItem "Perform calibration"
        End With
    Case Is = 33
        With TaskComboBox
            .AddItem "Preventive Maintenance"
        End With
    Case Is = 34
        With TaskComboBox
            .AddItem "Preventive Maintenance"
        End With
    Case Is = 35
        With TaskComboBox
            .AddItem "To perform Preventive Maintenance activities "
            .AddItem "To replace faulty EPKS modules (I/O, Controller and etc.)"
            .AddItem "To perform system software updates (patches and etc.)"
            .AddItem "To replace signal convertor devices (MOXA, ADAM and etc.)"
        End With
    Case Is = 36
        With TaskComboBox
            .AddItem "To perform Preventive Maintenance activities "
            .AddItem "To replace faulty SM/FSC modules"
            .AddItem "To perform system software updates (patches and etc.)"
        End With
    Case Is = 37
        With TaskComboBox
            .AddItem "To perform Preventive Maintenance activities "
            .AddItem "To replace faulty  modules"
        End With
    Case Is = 38
        With TaskComboBox
            .AddItem "Anti Virus update"
        End With
    Case Is = 39
        With TaskComboBox
            .AddItem "To perform vibration probe replacement"
            .AddItem "To perform proximitor replacement"
        End With
End Select


SizeComboBox.Clear


Select Case index
    Case Is = 0
        With SizeComboBox
            .AddItem "<<4inch"
            .AddItem "4 - 16 inch"
            .AddItem " 4 < x < 16 inch"
            .AddItem "16 < x < 32 inch"
            .AddItem "All"
        End With
    Case Is = 1
        With SizeComboBox
            .AddItem "<<4inch"
            .AddItem "4 - 16 inch"
            .AddItem " 4 < x < 16 inch"
            .AddItem "16 < x < 32 inch"
            .AddItem "All"
        End With
    Case Else
        SizeComboBox.Enabled = False
        OptionButton3.Enabled = False
End Select
Application.ScreenUpdating = True
End Sub


Private Sub FilterCommandButton_Click()
Application.ScreenUpdating = False
Call CancelCommandButton_Click
If OptionButton1.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=EquipComboBox.Value, Operator:=xlAnd
End If
If OptionButton2.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=TaskComboBox.Value, Operator:=xlAnd
End If
If OptionButton3.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:=SizeComboBox.Value, Operator:=xlAnd
End If
Application.ScreenUpdating = True
End Sub




Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
'Fill EquipComboBox
With EquipComboBox
    .AddItem "CONTROL VALVE"
    .AddItem "SHUTDOWN  VALVE"
    .AddItem "PRESSURE/ DP/ TEMPERATURE TRASMITTER"
    .AddItem "PRESSURE/ DP TRANSMITTER"
    .AddItem "TEMPERATURE TRANSMITTER"
    .AddItem "THERMOCOUPLE/RTD SENDOR"
    .AddItem "GUIDED WAVE RADAR"
    .AddItem "PRESSURE/ DIFFERENTIAL PRESSURE SWITCH"
    .AddItem "TEMPERATURE SWITCH"
    .AddItem "VIBRATION SWITCH"
    .AddItem "ORIFICE PLATE"
    .AddItem "FLAME SCANNER"
    .AddItem "GAS DETECTORS"
    .AddItem "SMOKE DETECTORS"
    .AddItem "FLAME DETECTOR"
    .AddItem "MANUAL CALL POINTS"
    .AddItem "FIRE ALARM PANEL"
    .AddItem "DELUGE SYSTEM"
    .AddItem "HEAT DETECTOR"
    .AddItem "METER PROVER"
    .AddItem "PIPE PROVER"
    .AddItem "FLOW COMPUTER"
    .AddItem "SKID METERING"
    .AddItem "GPTA METER"
    .AddItem "CCTV /PIDS"
    .AddItem "CONDUCTIVITY, DISSOLVED OXYGEN ANALYZER"
    .AddItem "PH ANALYZER"
    .AddItem "CO2, CO, NOX, SOX, H2S, DENSITORMETER AND CALORIMETER ANALYZER"
    .AddItem "MOISTURE ANALYZER (ALUMINIUM OXIDE)"
    .AddItem "MOISTURE ANALYZER (QUARTZ CRYSTAL MICROBALANCE)"
    .AddItem "O2 ANALYZER"
    .AddItem "GAS CHROMOTOGRAPH"
    .AddItem "RVP ANALYSER"
    .AddItem "ANAYLYZE HOUSE"
    .AddItem "COD ANALYZER"
    .AddItem "EPKS CONTROL SYSTEM"
    .AddItem "Safety Programmable Logic Controller (PLC) - Safety Manager (SM) & Fail Safe Controller (FSC)"
    .AddItem "Programmable Logic Controller (PLC) - Allen Bradley"
    .AddItem "Anti Virus Server"
    .AddItem "Vibration probe"
End With
Application.ScreenUpdating = True
End Sub

[AyaSofea]

Thank you so much, that works great!

Just one more question. I had a problem with the operator=:xlAnd. Suppose to be it will show the filter data that I exactly want to choose only, but it also shows others data if I use code operator:=xlAnd (sorry if I'm wrong). I can't figures out this problem.

Example code:

Private Sub FilterCommandButton_Click()

Application.ScreenUpdating = False
Call CancelCommandButton_Click
If OptionButton1.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=EquipComboBox.Value, Operator:=xlAnd
End If
If OptionButton2.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=TaskComboBox.Value, Operator:=xlAnd
End If
If OptionButton3.Value = True Then
    Call CancelCommandButton_Click: ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:=SizeComboBox.Value, Operator:=xlAnd
End If
Application.ScreenUpdating = True

End Sub

[Logit]

.
If you are filtering on a single field, I believe you can ignore using the 'Operator:=xlAnd'

I am not completely familiar with AutoFilter but what I've read indicates the 'Operator:=xlAnd' is to be utilized for two criteria. Example:

FILTERING DOWN TO SHOW 2 MATCHING CRITERIA

Let's now expand on the above by filtering down to show 2 criteria.

Sub FilterTo2Criteria()
With Sheet1
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
.Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _
Operator:=xlAnd, Criteria2:="<=45"
End With
End Sub

In the above code we have chosen to show all whose age is between 35 and 45. It's important to note that for the Operator argument we have used xlAnd. If we had used the other choice (XlOr) our results would be that of our original table. That is, all records would show as all people would be either >=35 or <=45.

The above is from : https://www.ozgrid.com/VBA/autofilter-vba-criteria.htm

Try removing that command from your macro and see if it functions as desired.

[AyaSofea]

Thank you again, for your reply and quick response to my problem.

[Logit]

.
You are welcome