+ Reply to Thread
Results 1 to 6 of 6

Filter - sort by criteria in multiple columns using Colour..

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Filter - sort by criteria in multiple columns using Colour..

    Hi,

    I'm trying to filter a list based on the fill colour of a cell.

    From the sheet attached, I want to filter it sothat the RED fills appear at the top and then the striped fill. I want to start with the right most column (T), and then repeat for the next column (S). I wish to repeat this from every column from T all the way to D. This will mean that the column with red in Column D will be at the top, and then ones in Column E and so on.

    I recorded the macro from filtering and got this (have changed activesheet to 'WS')

    Please Login or Register  to view this content.
    What I want to do is substitute
    Please Login or Register  to view this content.
    for
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.

    then I can increment the column by -1 for each loop.

    I'm afraid my VBA skills are still very patchy and I haven't grasped how to do this, and looking at other posts has confused me further!

    I'm sure there is a far more elegant solution to what I am trying to do, which I would be very much appreciative of, but I would also like to know how to achieve the above so that I can use the record macro as a base!



    To give a fuller picture, I should stipulate that the last column will not always be Col.T, and the row lengths will vary, which is why I need to use variables. Also, The step before this is to search for a known word (OUTPUT), and then sort the table as described above. There are many of these tables, so that is another loop which I will be adding once I get this first part done!

    The macro in the attached sheet is a 'Work in Progress', so please excuse!

    Any help would be greatly appreciated!

    J
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Filter - sort by criteria in multiple columns using Colour..

    Almost

    Range(Cells(3,20) ,Cells(49,20))
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Filter - sort by criteria in multiple columns using Colour..

    nathansav - thank you!
    (I feel like a bit of a muppet, but at least the problem has been solved!)

    Any better ways of executing this would be greatly received - I'm always happy to learn a more efficient way of doing something!

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Filter - sort by criteria in multiple columns using Colour..

    With myWS.AutoFilter.Sort.SortFields use for complete code block

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Filter - sort by criteria in multiple columns using Colour..

    Nathansav,
    It's not liking the .SortFields.Add line when I try it this way -
    Please Login or Register  to view this content.
    What am I not seeing now?!

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Filter - sort by criteria in multiple columns using Colour..

    For NextCol = LastCol To 4 Step -1


    do you have Next NextCol?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sort by multiple columns with sort criteria cell dependent
    By BRISBANEBOB in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-24-2014, 02:22 AM
  2. [SOLVED] Filter to show multiple columns that satistfy single criteria
    By singerj6 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-13-2014, 02:34 PM
  3. Filter Excel Data using vba multiple criteria multiple columns
    By pmyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2013, 01:32 AM
  4. Replies: 2
    Last Post: 06-15-2012, 05:34 PM
  5. Replies: 1
    Last Post: 02-03-2006, 12:55 PM

Tags for this Thread

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