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

Danoz

New Member
Joined
Jul 22, 2010
Messages
39
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,606
Office Version
365
Platform
Windows
What is your formula?
 

Danoz

New Member
Joined
Jul 22, 2010
Messages
39
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;

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

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




2S:T , AS:AT
2TRUE2TRUE
3S:V , AO:AP
3TRUE3TRUE
4S:X , AQ:AR
4TRUE4TRUE
5S:Z , AO:AP
5FALSE5TRUE
6S:AB , AO:AP
6FALSE6TRUE
7S:AD , AO:AP
7FALSE7TRUE
8S:AF , AO:AP
8FALSE8TRUE
9S:AH , AO:AP
9FALSE9TRUE
10S:AJ , AO:AP
10FALSE10TRUE
11S:AL , AO:AP
11FALSE11TRUE
12S:AN , AO:AP
12FALSE12TRUE

<tbody>
</tbody>



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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,606
Office Version
365
Platform
Windows
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)
 

Danoz

New Member
Joined
Jul 22, 2010
Messages
39
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,606
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,260
Messages
5,485,719
Members
407,511
Latest member
Tryintouseexcel

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top