How to write "If column-- is missing" in a formula

[uttamsaxena]

I write column no in the row below the row of column heading e.g.

Sr No Name Add1 Add2 Add3
1 2 3 4 5


Suppose I want to print all cols then it is OK but when I want to print Col 1, 4 and 5 then I hide Col 2 and 3. And then I change the no 4 and 5 with 2 and 3. To avoid this I want to write a formla like in cell containing no 4

= IF ( Column B and C are hidden, 2, 4)

So what should I write in place of the condition "Column B and C are hidden" in above formula.

 

[Blue Dragon]

Re: How to write "If column-- is missing" in a for

Well, in VB it would be:

If Columns(2).EntireColumn.Hidden = True And Columns(3).EntireColumn.Hidden = True Then
2
Else
4
End If

 

[Greg Truby]

Re: How to write "If column-- is missing" in a for

I don't know of any built-in functions that would do this. However you can write a UDF (User-Defined Function) in VBA that would work. Here's an example. Please note: even though this is Volatile, you need to hit your F9 key to force a ReCalc after you hide/unhide columns/rows.<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> IsHidden(ParamArray rngInput()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN><SPAN style="color:#007F00">' UDF to return whether a range is hidden</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rngCells<SPAN style="color:#00007F">As</SPAN> Range, rngCell<SPAN style="color:#00007F">As</SPAN> Range
    IsHidden =<SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> BadArgument
    Application.Volatile
    <SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(rngInput)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(rngInput)
        <SPAN style="color:#00007F">If</SPAN> TypeName(rngInput(i)) = "Range"<SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> rngCells = rngInput(i)
        <SPAN style="color:#00007F">ElseIf</SPAN> TypeName(rngInput(i)) = "String"<SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> rngCells = Range(rngInput(i))
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> rngCells =<SPAN style="color:#00007F">Nothing</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> rngCells<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rngCell<SPAN style="color:#00007F">In</SPAN> rngCells.Cells
                <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> rngCell.EntireColumn.Hidden _
                And<SPAN style="color:#00007F">Not</SPAN> rngCell.EntireRow.Hidden<SPAN style="color:#00007F">Then</SPAN> IsHidden =<SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> rngCell
        <SPAN style="color:#00007F">Else</SPAN>
            IsHidden =<SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
    <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Function</SPAN>

BadArgument:
    <SPAN style="color:#00007F">Set</SPAN> rngCells =<SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Resume</SPAN><SPAN style="color:#00007F">Next</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Example of applying this UDF (Note: in my worksheet, column C was hidden, but the HTML-maker utility is showing it.)

Test Hidden Ranges.xls
	A	B	C	D	E
1				T	F
2				F
3				F
4				FALSE	FALSE
Sheet1

HTH

 

[north19701]

Re: How to write "If column-- is missing" in a for

Sub Macro7()
Range("a2:f2").clearcontents
n = 1
For i = 1 To 6
If Columns(i).EntireColumn.Hidden = False Then
Cells(2, i) = n
n = n + 1
Else
End If
Next i
End Sub

Here's another way you could try, especially if you are using code to print as well.