Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Column Filtering via VBA

Posted on 2013-02-07
11
398 Views
Last Modified: 2013-02-07
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
Comment
Question by:elwayisgod
11 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 38864238
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
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38864487
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
 

Author Comment

by:elwayisgod
ID: 38864724
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 29

Expert Comment

by:gowflow
ID: 38864779
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
 

Author Comment

by:elwayisgod
ID: 38865020
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
 

Author Comment

by:elwayisgod
ID: 38865127
I get:  Compile Error:

Expected Funtion or variable
0
 

Author Comment

by:elwayisgod
ID: 38865212
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38865824
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
 

Author Comment

by:elwayisgod
ID: 38865848
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
 

Author Comment

by:elwayisgod
ID: 38865954
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38866007
just answered there
gowflow
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question