VB: Set AllCells = ActiveCell.CurrentRegion ActiveCell.CurrentRegion.Select With AllCells .Font.Size = 6 .Font.Bold = False .WrapText = True .EntireRow.AutoFit = True End With If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sub Cell_Resize() With Cells.SpecialCells(xlCellTypeVisible) .WrapText = True '''.EntireColumn.AutoFit .EntireRow.AutoFit End With End SubCheers
if cell().entirecolumn.hidden=true and 2nd is speical cell property Rng.SpecialCells(xlCellTypeVisible),Both r not working
VB: Function CountUniqueValues(InputRange As Range) As Long Dim cl As Range, UniqueValues As New Collection InputRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True For Each cl In InputRange If Not cl.Entirerow.Hidden Then UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item Next cl On Error Goto 0 CountUniqueValues = UniqueValues.Count End Function If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesRight now, the function does not work but the layout is correct. I was wondering if any expert programmers can reorganize the layout for me? It's supposed to create an array of all the unique values. Any duplicates are not added to this array and at the end, a count of this array is provided. It does not work after I added the If statement there but I need that condition as I want to count the number of unique entries within a filter. Hence, if there are hidden lines, this function would calculate the total number of unique entries only visible at that time. Perhaps this method of creating an array is not feasible; are there any suggestions? I think the approach is somewhat working but I need an alternative to simply making another array. Thanks alot.