Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt hiding columns?
I have the following code which hides blank columns in my autofilter, but
this was set up to work for rows 3 downawards. I'd like to adapt it to work in another sheet, starting at row 2. Any ideas? Thanks. Sub ABC() Dim lastColumn As Long, rngA As Range, i As Long Dim rng1 As Range Application.ScreenUpdating = False Columns.Hidden = False lastColumn = Me.Cells(2, "IV").End(xlToLeft).Column Set rngA = Me.AutoFilter.Range Set rngA = rngA.Offset(1, 0) _ .Resize(rngA.Rows.Count - 1, 1) For i = 4 To lastColumn Set rng1 = Cells(3, i).Resize(rngA.Rows.Count, 1) If Application.Subtotal(3, rng1) = 0 Then Columns(i).Hidden = True End If Next Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt hiding columns?
If I understand you correctly, then all you need to do is change 3 to 2
in the following row: Set rng1 = Cells(2, i).Resize(rngA.Rows.Count, 1) ''set range to start from row 2 instead of 3 But I'm not sure if this really is all that you are after. Regards, Steve PG Oriel wrote: I have the following code which hides blank columns in my autofilter, but this was set up to work for rows 3 downawards. I'd like to adapt it to work in another sheet, starting at row 2. Any ideas? Thanks. Sub ABC() Dim lastColumn As Long, rngA As Range, i As Long Dim rng1 As Range Application.ScreenUpdating = False Columns.Hidden = False lastColumn = Me.Cells(2, "IV").End(xlToLeft).Column Set rngA = Me.AutoFilter.Range Set rngA = rngA.Offset(1, 0) _ .Resize(rngA.Rows.Count - 1, 1) For i = 4 To lastColumn Set rng1 = Cells(3, i).Resize(rngA.Rows.Count, 1) If Application.Subtotal(3, rng1) = 0 Then Columns(i).Hidden = True End If Next Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Columns | Excel Discussion (Misc queries) | |||
hiding columns | Excel Discussion (Misc queries) | |||
Hiding columns | Excel Programming | |||
Hiding columns | Excel Programming | |||
Hiding Columns | Excel Programming |