iamgujju
06-26-2019, 11:29 AM
Evening all,
First of all i am new here and learning new things with VBA code.
I have been to other Excel/VBA forum too and someone have helped me with code....which you can see below....
i have got data range C6 to CA1000.... columns and rows contain data....and i have created drop-down box at cell B3.
if i select "Monday" from drop-down....only those rows and columns required visible; which contain "Monday" otherwise hide the rest.
the below mentioned code does work but it hides those data which contain "Monday".... could i request for some experts help please.
for your reference i have attached my test2 file too; please use test2 file.
your help would be much appreciated; thanks in advance.24500
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRange As Range
Dim col As Range, row As Range
Set dataRange = Range("c6:ca1000")
If Not Intersect(Target, Range("B3")) Is Nothing And Target.Count = 1 Then
Application.ScreenUpdating = False
dataRange.Columns.Hidden = False
dataRange.Rows.Hidden = False
If Target.Value <> "ALL" Then
For Each col In dataRange.Columns
If WorksheetFunction.CountIf(col, Target.Value) > 0 Then col.Hidden = True
Next
For Each row In dataRange.Rows
If WorksheetFunction.CountIf(row, Target.Value) > 0 Then row.Hidden = True
Next
End If
Application.ScreenUpdating = True
End If
End Sub
First of all i am new here and learning new things with VBA code.
I have been to other Excel/VBA forum too and someone have helped me with code....which you can see below....
i have got data range C6 to CA1000.... columns and rows contain data....and i have created drop-down box at cell B3.
if i select "Monday" from drop-down....only those rows and columns required visible; which contain "Monday" otherwise hide the rest.
the below mentioned code does work but it hides those data which contain "Monday".... could i request for some experts help please.
for your reference i have attached my test2 file too; please use test2 file.
your help would be much appreciated; thanks in advance.24500
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRange As Range
Dim col As Range, row As Range
Set dataRange = Range("c6:ca1000")
If Not Intersect(Target, Range("B3")) Is Nothing And Target.Count = 1 Then
Application.ScreenUpdating = False
dataRange.Columns.Hidden = False
dataRange.Rows.Hidden = False
If Target.Value <> "ALL" Then
For Each col In dataRange.Columns
If WorksheetFunction.CountIf(col, Target.Value) > 0 Then col.Hidden = True
Next
For Each row In dataRange.Rows
If WorksheetFunction.CountIf(row, Target.Value) > 0 Then row.Hidden = True
Next
End If
Application.ScreenUpdating = True
End If
End Sub