• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Column Filtering via VBA

Hi,

I have spreadsheet that has column headings in row 13.  I have a hidden row 14 that must remain untouched.  I want to do data filtering on the columns but the buttons need to be on row 13 and ignore row 14.  Is this possible via vba?
0
elwayisgod
Asked:
elwayisgod
1 Solution
 
Rgonzo1971Commented:
Hi,

You could have a helper column (hidden) for example AA

In cell AA14  insert 1 and filter in AA13 does not equal 1

Of course if you clear filter it will appear as well.

Regards
0
 
Martin LissOlder than dirtCommented:
You can do that without VBA. Select the whole column (or the range starting with the 1st cell you want to have dropdowns, and select Data|Data validation. If you get a message about some cells not having data validations, choose the "Yes" button and apply the validation you want. Then select the cell(s) in the column or range in which you don't want dropdowns, go to Data Validation and select 'Clear All'.
0
 
elwayisgodAuthor Commented:
OK.. I'm trying and it's not working.  In Cell L13 I want to be able to select a value and the range of values is from L15 to L3014.  When selecting the value, it then acts as a filter and will only show the rows of the values selected.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gowflowCommented:
yes it is possible. Look at this file and activate macroes and try the button and filters as well
ansee how you can manipulate row 14 to show and hide and not affected by filtering.
gowflow
AutoFilter.xls
0
 
elwayisgodAuthor Commented:
This is really interesting.. Now understand I'm newbie to buttons and vba etc..  Basically I tried creating the button the way you have it.  Except I don't need it to unhide row 14.  Row 14 will always be hidden.  Basically I need it to activate and unactivate filtering only.  Columns B to AC will need filtering only for rows 15 to 3014 for each column.  This is for the Retrieval tab.
Template-Final-020513-v12.xlsm
0
 
elwayisgodAuthor Commented:
I get:  Compile Error:

Expected Funtion or variable
0
 
elwayisgodAuthor Commented:
I changed the name of macro back to CommandButton1_Click and  now I get:

Run-time error '424':  Object required.  When I hit debug this row is highlighted:


If CommandButton1.Caption = "Show Filter and Hide Row 14" Then
0
 
gowflowCommented:
its ok here it is.
By the way you should not post real data !!! too many hackers around !!! watch out for next time.
gowflow
Template-Final-020513-v12.xlsm
0
 
elwayisgodAuthor Commented:
It's fake data!! :)  Customer names random and actual data is garbage.  I just posted another quesion on adding a button to turn on/off the Calculation function too.  I'm almost done.  Appreciate the help.
0
 
elwayisgodAuthor Commented:
actually i just change the protection on the whole sheet from row 14 to 12 instead.  14 is hidden and it they muck with 13 then so be it.   Move onto button for the autocalc thing
0
 
gowflowCommented:
just answered there
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now