Visible cells 1

[Cage]

I am trying to use a function to return the address of visible cells in a specified range.

e.g

Assume addv is the range B2:B10 and that cells B3 and B4 are hidden.

Function addvisible(addv As Range)
addvisible = addv.SpecialCells(xlCellTypeVisible).Address
End Function

The above code returns the address as $B2:B10 instead of $B2,$B5:$B10.

However if I use the following Sub,

Sub addvisible2()
MsgBox Range("G34:R34&quot.SpecialCells(xlCellTypeVisible).Address
End Sub

It returns $B2,$B5:$B10 as required.

Can any suggest how I can modify my function to return the required range and also why the results from the function and sub are different.

Thanks

Cage

 

[RobBroekhuis]

Both syntaxes/methods work just fine for me. Are you sure that the range reference in your function refers to the correct worksheet?
Rob

 

[Cage]

Yip,

The range references are correct as I only have one sheet in the workbook. For some reason using fuction just does not seem to recognise hidden cells?!?

 

[RobBroekhuis]

No, I seriously doubt it has anything to do with the fact that it's a function. I tested the following function:

Function vc(r As Range) As String
vc = r.SpecialCells(xlCellTypeVisible).Address
End Function

and ?vc(range("B1:B10&quot) returns the proper multi-area range. You did not put the exact code in your first post (the range reference doesn't make sense) - can you quote the exact code giving the problem, and the spreadsheet cells that are hidden? I'm assuming you have hidden rows in your sheet.

Rob

 

[Cage]

Rob, I tried your code and I still get the same result.
ie if I hide columns B,C,E and in the cell G1 enter the following formula =vc("A1:F1&quot

i get G1 showing $A$1:$F$1 when really i want it to show $A$1,$D$1,$F$1

Could this problem be solved by changing any excel settings?

 

[RobBroekhuis]

Very interesting... I just reproduced the behavior. I wasn't aware that you were using it as a WORKSHEET function (the function works just fine from the immediate window). I just set a breakpoint and executed it as a worksheet function - now even in the immediate window it's not working right. I'm going to play around with this some more. Anybody else have some insights into how Excel executes differently while in calculation mode?
Rob

 

[RobBroekhuis]

Just another observation. While in break mode, I typed in the immediate window:

?range("a1:f1&quot.SpecialCells(xlCellTypeVisible).Address

and got $A$1:$F$1

Then I hit the Stop button to terminate the function execution, hit enter again on the same line of code in the immediate window, and now got:

$A$1,$D$1,$F$1

Apparently the specialcells method doesn't work during calculation mode. If you can tell us something more about the underlying problem you're trying to resolve, I think we can come up with a workaround.
Rob

 

[Cage]

ok i have managed to get around the problem using the following code:

Function vc(myrange As Range)
addvisible = 0
For k = 1 To (myrange.Columns.Count)
If myrange.Columns(k).Hidden = False Then
addvisible = addvisible + Cells(myrange.Row, myrange.Columns(1).Column + k - 1).Value
End If
Next k
vc = addvisible
End Function

however, i now have the problem with the function updating/calculating. ie if i hide a column the cells containing the function do not update. I can't even update using F9. The only way I get the cells to update is to edit the code in the editor then switch back to the worksheet and press F9. Ideally I would like the function to be recalculated everytime I hid or unhide columns. Any ideas?

Cage

 

[RobBroekhuis]

In many cases, adding "application.volatile" as the first line in your function will help it be recalculated when the spreadsheet contents change. I doubt it will help you in this case, because formatting changes don't trigger recalculation (I think). Only way I can think of to accomplish what you're trying to do is to give your function an additional dummy parameter, which always refers to an otherwise unused cell in the spreadsheet, and update this cell in the worksheet_selectionchange event, to force a recalc of your function. Ugly, but I think it would work.
Rob

 

[Cage]

Rob,

The application.volatile command, works a treat. I can now use F9 to recalculate.

Cheers

Cage