Protected worksheet - filter on one column only?

[TheWennerWoman]

Hello,

I was pointed in this direction by a colleague who told me if I needed an answer to an Excel question, then this was the place to be. So here I am

I have a spreadsheet (Office 365). One of the worksheets is 99% protected - there are a couple of cells that the users can enter parameters that are passed into a SQL query.

I have been asked if it's possible for one user to be able to filter a single column (column C).

I can't see any way to do this; it seems that when you protect the sheet, you can tick to allow autofiltering but that applies to every column whereas I just need column C to be unprotected.

Is this possible?

Many thanks for your time.

 

[Fluff]

If you unprotect the sheet, select column C, on the data tab select "Filter", then reprotect the sheet.
You can then only filter on col C

 

[TheWennerWoman]

If you unprotect the sheet, select column C, on the data tab select "Filter", then reprotect the sheet.
You can then only filter on col C

Ahh, that works on a normal data range.

I have discovered today that it's a table; so you either have a filter on all columns or none.

Thank you for your reply, is anything possible with a table?

 

[Fluff]

It is possible, but it will need VBA. Is that OK?

 

[Fluff]

Iy you're Ok with VBA.
Unlock the sheet & run this

Sub TheWennerWoman()
   Dim i As Long
   
   With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").ListObjects("[COLOR=#ff0000]table1[/COLOR]")
      For i = 1 To .HeaderRowRange.Columns.Count
         If i <> 3 Then .Range.AutoFilter Field:=i, VisibleDropDown:=False
      Next i
   End With
End Sub

Change sheet name & table name in red to suit.
Then reprotect the sheet & you should be good to go.

 

[TheWennerWoman]

Iy you're Ok with VBA.
Unlock the sheet & run this

Sub TheWennerWoman()
   Dim i As Long
   
   With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").ListObjects("[COLOR=#ff0000]table1[/COLOR]")
      For i = 1 To .HeaderRowRange.Columns.Count
         If i <> 3 Then .Range.AutoFilter Field:=i, VisibleDropDown:=False
      Next i
   End With
End Sub

Change sheet name & table name in red to suit.
Then reprotect the sheet & you should be good to go.

Outstanding, how you guys do this stuff is beyond me.

Many thanks.

 

[Fluff]

You're welcome & thanks for the feedback