Solved

Column Filtering via VBA

Posted on 2013-02-07
11
391 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 48

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 45

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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now