| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help- It's only a tiny change
Hi,
I have the code below which hides empty columns when I autofilter from column D onwards. The autofilter data sits in column C. All I want is the Column data to sit in column D and it hide columns from E onwards. What little thing do I change? 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(1, "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
|
|||
|
|||
|
Help- It's only a tiny change
The for loop count has to start at 3 (Col C) instead of 4 (Col D).
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(1, "IV").End(xlToLeft).Column Set rngA = Me.AutoFilter.Range Set rngA = rngA.Offset(1, 0) _ .Resize(rngA.Rows.Count - 1, 1) For i = 3 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 "PG Oriel" wrote: Hi, I have the code below which hides empty columns when I autofilter from column D onwards. The autofilter data sits in column C. All I want is the Column data to sit in column D and it hide columns from E onwards. What little thing do I change? 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(1, "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help- It's only a tiny change
Hi, shouldn't it be 5 it I want it to filter from column E onwards and not 3?
"Joel" wrote: The for loop count has to start at 3 (Col C) instead of 4 (Col D). 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(1, "IV").End(xlToLeft).Column Set rngA = Me.AutoFilter.Range Set rngA = rngA.Offset(1, 0) _ .Resize(rngA.Rows.Count - 1, 1) For i = 3 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 "PG Oriel" wrote: Hi, I have the code below which hides empty columns when I autofilter from column D onwards. The autofilter data sits in column C. All I want is the Column data to sit in column D and it hide columns from E onwards. What little thing do I change? 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(1, "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help- It's only a tiny change
Yes 5 would be correct after I read your original posting.
"PG Oriel" wrote: Hi, shouldn't it be 5 it I want it to filter from column E onwards and not 3? "Joel" wrote: The for loop count has to start at 3 (Col C) instead of 4 (Col D). 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(1, "IV").End(xlToLeft).Column Set rngA = Me.AutoFilter.Range Set rngA = rngA.Offset(1, 0) _ .Resize(rngA.Rows.Count - 1, 1) For i = 3 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 "PG Oriel" wrote: Hi, I have the code below which hides empty columns when I autofilter from column D onwards. The autofilter data sits in column C. All I want is the Column data to sit in column D and it hide columns from E onwards. What little thing do I change? 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(1, "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 | |||
| tiny texas houses, tiny houses, tumbleweed tiny house company, tinyhouse, small houses, tiny texas houses of luling | Excel Worksheet Functions | |||
| Tiny font at print time | New Users to Excel | |||
| How do I turn off the tiny icons? | Excel Discussion (Misc queries) | |||
| Tiny graph | Charts and Charting in Excel | |||
| Validation - Drop Down Lists with tiny text | Excel Discussion (Misc queries) | |||
Linear Mode
