wrong code?

[minostrada]

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

 

[Bob Phillips]

In what way does in not work?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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

 

[minostrada]

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]

Are the cells really empty or do they just look empty because you have some
formula like

=If(condition,"",formula)

 

[Tom Ogilvy]

In the meantime, I tested the code with an autofilter applied to a range
that had actual blank cells (not cells with formulas that looked blank) and
it worked fine for me. ran it and it hid the all blank columns (and they
did have headers). I then picked another criteria and ran it. Previously
blank columns were unhidden and new blank columns were hidden.

 

[minostrada]

Tom,

My fault! It works fantastic, I forgot to use the macro! I thought it worked
already when I use the autofilter.

sorry! but it works great!

thanks