+ Reply to Thread
Results 1 to 17 of 17

Filter rows based on cell values in multiple columns

  1. #1
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Filter rows based on cell values in multiple columns

    Hi,

    I have a large spreadsheet ranging from A1 to CC572. The 572 rows are for the crews, machinery, etc and the A:CC columns are for dates/hours and costing (E.g. Column J: Date/Hours, Column K: Cost, Column L: New Date/Hours, Column M: New Cost etc). This is a template for projects but want to avoid the time consuming process of hiding all rows that are note related any particular project for printing. I am looking for a macro to apply to a button that when clicked will hide all unrelated rows?

    I have attached the spreadsheet.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    Can you point out the unrelated rows so we can help you?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    See attached. The unrelated rows are the rows that do not have any values in columns J onwards.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    Hi Arlette,

    I understand if you are busy, but just wanted to confirm that the information I sent in reply to your request was sufficient?

    Regards,

    Daniel

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    Are you confusing the rows with columns? Do you mean to say that if there is no value from column J onwards, it should be hidden?

  6. #6
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    Yes but let me restate what you said to be sure we are on the same page.

    If there are no values in a row from column J onwards, then that row should be hidden". Conversely, if there are values in a row from column J onwards, then that row should remain un-hidden.

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    So which is the last column to be checked from column J onwards?

  8. #8
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    The last column is Column CC.

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    So to be on the same page with you, rows 12, 14, 16, etc should be hidden right?

  10. #10
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    That is correct.

  11. #11
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    What if in future, you want to enter some related data into these rows in columns J onwards, how do you want to unhide the rows? Will it be done manually?

  12. #12
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    I will create a 'Clear' button with the code below so anyone can clear the filter and input or update data at any time.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    The code you gave is used to clear the filters. But you need code to hide rows. That is different.

  14. #14
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    Instead, I can use unhide all rows macro.

  15. #15
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    Try these 2 sets of code. Code 1: Hides the unrelated rows
    Please Login or Register  to view this content.
    Code 2: Unhides the rows in the entire sheet.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    05-11-2012
    Location
    Gold Coast
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Filter rows based on cell values in multiple columns

    Hi Arlette,

    I tried the two codes, but they come up with a "Run-time error '9': Subscript out of range". It does this with the line of code coloured in blue below.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Filter rows based on cell values in multiple columns

    Oops i forgot to mention this to you that you have an additional space in your sheetname after Summary. Remove that space and run the macro and it should be good to go.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1