What is your formula?
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.
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
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.
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
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!
Ok, if you change the formula in row 1 to
=IF(VLOOKUP(S10,"Table above",2, FALSE ),"",FALSE)
you can useCode: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
Like this thread? Share it with others