Are the cells really empty or do they just look empty because you have some
formula like
=If(condition,"",formula)
--
Regards,
Tom Ogilvy
"minostrada" wrote in message
...
Tom,
I copied en pasted code into my VB. The empty columns are still visible,
nothing disappears. But your question was totally right, I want to hide
columns if the entire column in the filter range only has empty cells when
the data is filtered.
Above the data with the autofilter are my columnheaders, maybe excel
thinks
because of this that the column isn't empty. But the range we've selected
is
only the autofilter range, so I don't get it!
What am I doing wrong?
rens
"Tom Ogilvy" wrote:
do you want to hide columns if the entire column in the filter range
only
has empty cells when the data is filtered? If so modify the code like
so:
Sub Tester01()
Dim rng As Range
Dim col As Range
if Activesheet.AutoFilterMode then
Application.ScreenUpdating = False
Set rng = ActiveSheet.AutoFilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.EntireColumn.Hidden = False
for each col in rng.columns
if application.Subtotal(3,col) = 0 then
col.EntireColumn.Hidden = True
end if
Next col
else
msgbox "No Autofilter applied"
end if
Application.ScreenUpdating = True
End Sub
--
Regards,
Tom Ogilvy
"minostrada" wrote in message
...
I got this code but it doesn't work.
Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col As Range
Application.ScreenUpdating = False
On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range
rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If
Application.ScreenUpdating = True
End Sub
What am I doing wrong? Do I have to change the cellproperties from
standard
or number to text? Or am I totally looking in the wrong direction?
let me know!
rens