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

1. ## 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.

3. ## 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. ## 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)

5. ## 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. ## 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```