+ Reply to Thread
Results 1 to 10 of 10

(edited title) BUG filtering for blanks (or filtering out all empty rows)

  1. #1
    Registered User
    Join Date
    10-14-2010
    Location
    Stephenville, Texas
    MS-Off Ver
    Excel 2010
    Posts
    78

    (edited title) BUG filtering for blanks (or filtering out all empty rows)

    Per forum rules my other post is getting deleted and here it is with amended title.
    I started a new thread to clean up the mud in my head from the post I have here:
    https://www.excelforum.com/excel-pro...ml#post4678882

    Goal is simple. My data set has headings in row 1. All data is nicely at the top as in first few rows. All rows are empty below the last row containing any data. In the attached sample worksheet, column A has values, then there are values in K, M & N.

    This will all happen in sheet2 (second tab):
    1. Fill any blanks between values in columns K & N with the value "No current UPC". The sample worksheet has one blank in each of these columns.
    2. Filter column K for cells with yellow fill color and move those cells to column R
    3. Clear filters
    4. Filter column R for blanks. IF no blanks, THEN exit sub. (this is where i keep bugging out. Tried coloring every blank row purple to filter them out, but did not work. Tried populating "xxx" in every blank row to filter them all out. Seems more plausible but cannot seem to make it work. Post referenced in link)
    5. Filter column N for cells with yellow fill color and move those cells to column R
    6. Clear filters
    7. Filter column R for blanks. IF no blanks, THEN exit sub.
    8. Move data from columns K & N to column R using logic formula. Formula = "=IF(RC[-7]<>""No current UPC"",RC[-7],RC[-4])"
    9. Clear filters
    10. Filter for orange fill color in column M. IF no orange cells are present, THEN exit sub.
    11. Populate column V with formula. Formula = "=IF(RC[-11]=""No current UPC"",""No current UPC"",(RC[-8])"
    12. Clear filters
    13. Remove formula from column V (paste values)
    14. Filter for green fill color in column M. IF no green cells are present, THEN exit sub.
    15. Populate column T with formula. Formula = "=IF(RC[-9]=""No current UPC"",""No current UPC"",(RC[-6])"
    16. Clear filters
    17. Remove formula from column T (paste values)
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,150

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    I have closed your other thread. However, in future, if this happens again (I hope it won't), please do what you are asked to do. You can change your thread title by editing the first post and then going advanced. I would not ask you to do something you cannot do! Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    Hey zextrot,

    Do these steps:

    Turn on the Macro Recorder and do your steps from 1 to 17. Then turn off the recorder and look at the macro Excel has recorded. If you want the exact same thing next time, simply run the macro.

    https://support.office.com/en-us/art...5-3ea70e64937b
    https://www.bing.com/videos/search?q...49&FORM=VRDGAR

    Post the workbook with the recorded macro and explain what you think will need to be changed.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    10-14-2010
    Location
    Stephenville, Texas
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    I'm more of an intermediate level guy who struggles with certain things sometimes. I can record and then debug step by step. I spend a lot of time thinking thru the logic of what's needed in a project and attempt to choose the most streamlined coding strategy. Below is what I have so far. I've been thru multiple versions and what follows is my current draft. Most of this macro works just fine. The part that bugs out is when I try to filter for blanks.
    I've attempted to fill all blank rows with a color and filter that out but it does not work. I've tried to fill all blank row cells with "xxx" to filter out that value, but the range selection is too large to populate the value.

    Please Login or Register  to view this content.
    Last edited by zextrot; 06-20-2017 at 08:44 AM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    Hi Zextrot,

    I learned a long time ago from teylyn that "colors aren't data", which means trying to use colors in filtering and sorting is not a good idea.

    If you really need VBA to do all the steps you suggest above then lets break them down into smaller steps or Subs. Then create a macro that calls all the smaller subs in the correct order.
    This would do your step 1 from above:
    Please Login or Register  to view this content.
    I don't know why you want to filter in all your steps. A different/better way is to simply check all the visible cells and do an IF check to see if they meet your filter criteria. If they meet your RGB numbers then do the step.

    Another hint in a lot of code is to create Dynamic Named Ranges for your data as it may shrink or grow daily. Then use the DNR in the code instead of fixed ranges.

  6. #6
    Registered User
    Join Date
    10-14-2010
    Location
    Stephenville, Texas
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    You make a good point .. if my logic reasoning can be simplified, that would simplify the macro.
    The logic behind the filtering is to choose the right part# from a particular column and move it to another column based on criteria.

    BEST MATCH COLUMN R:
    Move all part numbers with yellow fill color from columns K & N to column R, prioritizing column K in instances where both columns have yellow fill in that row.
    Regarding the rows that are remaining that do not have yellow fill, move any cells with a part# from K to R. If no part# in K, move whatever value is in N to R even if not a part#.

    UPGRADE COLUMN V:
    Regarding only the rows with orange fill color in column M, move all part#s from column N to column V if they are not already in column R.

    ECONOMICAL COLUMN T:
    Same as Upgrade column just regarding the rows with green fill color in column M instead of orange.

  7. #7
    Registered User
    Join Date
    10-14-2010
    Location
    Stephenville, Texas
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    IF someone would help me with the logic statement for the above section of the macro, THEN I feel confident I can handle the remainder of my overall project. (pun intended)

  8. #8
    Registered User
    Join Date
    10-14-2010
    Location
    Stephenville, Texas
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    May be easier to have it go thru row by row applying criteria, and can be 3 separate subroutines that run in order:

    POPULATING BEST MATCH COLUMN R:
    -IF K2 has yellow fill, THEN copy the value in K2 to R2.
    -IF K2 does NOT have yellow fill, and N2 does have yellow fill, THEN copy value in N2 to R2.
    -IF K2 does NOT have yellow fill, and N2 does NOT have yellow fill, and K2 has a value that is a part#, THEN copy K2 to R2.
    -IF K2 does NOT have yellow fill, and N2 does NOT have yellow fill, and K2 does not have a part# as the value, THEN copy whatever value exists in N2 to R2 whether it is a part# or not.

    POPULATING UPGRADE COLUMN V:
    IF M2 has orange fill, and the value in N2 does not already appear in R2, then copy the value in N2 to V2.

    POPULATING ECONOMICAL COLUMN T:
    IF M2 has green fill, and the value in N2 does not already appear in R2, then copy the value in N2 to T2.


    (cross posted on http://forum.chandoo.org/threads/nee...atement.34816/)

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    Hi zextrot,

    When I suggested above that "colors aren't data", I wanted you to not use colors to do any of your VBA work. If you had a column called "Yellow" and put an "X" in the rows that were colored yellow, you could write vba to go through that column looking for Cells(RowCtr,"??")="X" and do what you need using VBA. The problem with colors is there are so many of them to consider. If you have true yellow it might be called vbYellow or it might be something close to a RGB(?,?,?) color. I don't see any VBA in your example above but hope by writing small steps you can get closer to your answer.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: (edited title) BUG filtering for blanks (or filtering out all empty rows)

    I wrote this for your original post - see whether it's still relevant:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ 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. Auto sorting/filtering data without empty rows to generate scatter plots
    By turkeyusa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2014, 02:05 PM
  2. Filtering out zeros and blanks
    By hbusche in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-25-2013, 03:29 PM
  3. Filtering with blanks
    By Shadmani in forum Excel General
    Replies: 3
    Last Post: 02-01-2012, 04:36 AM
  4. Do not show all blanks rows after a filtering
    By larics in forum Excel General
    Replies: 1
    Last Post: 12-16-2011, 06:43 AM
  5. Advanced filtering for non blanks.
    By pdmkh in forum Excel General
    Replies: 2
    Last Post: 01-17-2011, 04:04 PM
  6. [SOLVED] Advanced filtering on text and blanks
    By dtencza in forum Excel General
    Replies: 4
    Last Post: 03-13-2006, 09:10 PM
  7. [SOLVED] filtering out blanks..
    By Hassan Alameh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2005, 09:06 AM

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