Solved

Column Filtering via VBA

Posted on 2013-02-07
11
397 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 49

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell add exchange property to a report 12 49
Excel Formula 5 43
Activation for Microsoft Office for Mac 2016 3 32
Excel Question 17 15
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…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

785 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