Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
In what way does in not work?
-- HTH RP (remove nothere from the email address if mailing direct) "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
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. -- 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong code?
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 "minostrada" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with this code? | Excel Programming | |||
What's wrong with this code??? | Excel Programming | |||
what's wrong with my code???? | Excel Programming |