Forum Discussion
Excel filter and hide other columns
- Jun 15, 2022
I have attached a small demo workbook. You'll have to modify the code for your situation.
To view the code, right-click the sheet tab and select 'View Code'.
You'll need to allow macros when you open the workbook.
You can copy the code from the worksheet module in the demo workbook to the worksheet module in your own workbook.
You'll then have to modify it because your worksheet probably uses different columns for the partners.
You'll have to save it as a macro-enabled workbook (.xlsm).
If you wish, you can attach a copy of your workbook without sensitive data, or make it available through OneDrive, Google Drive, Dropbox or similar.
- BeyK40Jun 15, 2022Brass Contributor
Still doesn't work.. I've copied the VBA and modified it, but it still didn't work.
Below my workbook without data. Ive 5 sheets in one workbook, so I've made a new workbook with only 1 sheet.
- Charla74Jun 15, 2022Iron Contributor
@BeyK40
Hopefully @Hans_Vogelaar doesn't mind me jumping in here and you should mark his response as 'Best Answer' as the hard yards are already done....
In your workbook, you have saved the code as modules, whereas a worksheet change routine needs to be in the MS Excel Objects for the specific worksheet. The next thing is that you need a data validation list for the filter items where the code is monitoring for the change. See attached the updated version:
- BeyK40Jun 16, 2022Brass Contributor
Thanks!
One more question.
The cells with te partners refer to another sheet (main sheet). With data valadition there is no problem, still get a list to filter. But the code doesnt work.. Is there a way to fix this?
(the code does work if I just type in the partner, but I it has to refer to the main sheet, because if a partner changes, I wanted it to automatically change in all the sheets.)