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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top