?
Solved

Column Filtering via VBA

Posted on 2013-02-07
11
Medium Priority
?
402 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
[X]
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
11 Comments
 
LVL 52

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 48

Accepted Solution

by:
Martin Liss earned 1500 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 31

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 31

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 31

Expert Comment

by:gowflow
ID: 38866007
just answered there
gowflow
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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