For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54
Results 1 to 6 of 6

Thread: For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54

    Hi all

    I have an IF formula resulting in TRUE or FALSE for S1:AT1.

    When false, I want to select rows 10:54 in that column to delete and shift left. Only cell/formula that may be disrupted is in row 1. There is data beneath row 54 that needs to remain so cannot delete entire column.

    From searching the forum, I think I might need to use a UNION, but have never done so, and hoping someone could please assist?

    Thank you.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,340
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54

    What is your formula?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54

    I have a set of variable data provided by user during workbook setup. The workbook determines a MAX value (named) based on the data, which is between 2 & 12. MAX determines which columns to keep in the current worksheet.

    The IF formula simply looks up whether the column is required for this set of data. I have a sub-list worksheet with a lookup similar to this;

    MAX x
    In other words, e.g., if MAX = 4 Or if MAX = 12

    (^^populated by user data^^)
    This and next columns are how my lookup table appears
    When MAX = Then columns ... are TRUE




    2 S:T , AS:AT
    2 TRUE 2 TRUE
    3 S:V , AO:AP
    3 TRUE 3 TRUE
    4 S:X , AQ:AR
    4 TRUE 4 TRUE
    5 S:Z , AO:AP
    5 FALSE 5 TRUE
    6 S:AB , AO:AP
    6 FALSE 6 TRUE
    7 S:AD , AO:AP
    7 FALSE 7 TRUE
    8 S:AF , AO:AP
    8 FALSE 8 TRUE
    9 S:AH , AO:AP
    9 FALSE 9 TRUE
    10 S:AJ , AO:AP
    10 FALSE 10 TRUE
    11 S:AL , AO:AP
    11 FALSE 11 TRUE
    12 S:AN , AO:AP
    12 FALSE 12 TRUE



    I also have a few different totals columns, which I was going to amend the script to keep whatever set is relevant. As 2 and 4 are most common MAX values, there are sum totals columns in AS:AT for MAX = 2, and AQ:AR for MAX = 4. If you have a way to either keep the totals as a single set, without running into circular or REF errors after deleting parts of the sums, or a better solution, for that I'd be even more appreciative!

    Let me know if any of that isn't clear, and thanks for helping.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,340
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54

    As you have not answered my question, I'll put it another way.
    Lets say you had a formula in row 1 like
    =IF(MAX(A2:A100)=4,TRUE,FALSE)
    would you be happy to change it to
    =IF(MAX(A2:A100)=4,"",FALSE)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jul 2010
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54

    S10 to AT10 are basically labelled 2, 2, 3, 3, 4, 4, etc to 12, 12, then 6 columns with total, variance, total, variance, total, variance.

    S1 looks up S10 (i.e. 2) in the table I posted and returns the true/false result. In the table the formula (using variable terms) is

    = IF ( R(0)C(-1) <= MAX , TRUE , FALSE )

    The formula in S1 is

    = VLOOKUP ( S10 , "Table above" , 2 , FALSE )

    If you can assist and need to change any formula, I have no concerns with it, especially if you speed up the script in the process!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,340
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: For columns where row 1 returns FALSE, select, clear, delete cells in rows 10:54

    Ok, if you change the formula in row 1 to
    =IF(VLOOKUP(S10,"Table above",2, FALSE ),"",FALSE)
    you can use
    Code:
    Sub danoz()
       Intersect(Rows("10:54"), Range("S1:AT1").SpecialCells(xlFormulas, xlLogical).EntireColumn).Delete xlToLeft
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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