Hi VBA expert community!
I am looking for some help on hiding columns with the result from autofilter. I have no experience in coding with autofilter result.
After I applied autofilter in column 1, I need the code that would hide the entirecolumn when there are no number (or is blank, or sum is zero) in the visible cells of each range of column.
The original data is in sheet1 and the result that I want in the 2nd sheet. I am using Excel 2003.
Many thanks!
[vba]
Sub CopyData()
Dim rng As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Copy Worksheets("Sheet2").Range("A1")
End If
Set rng = Nothing
End Sub
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
[VBA]
Option Explicit
Private Sub Worksheet_Calculate()
Dim rng1 As Range, Rng2 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng1 = Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))
Set Rng2 = rng1.SpecialCells(xlCellTypeVisible)
Columns.Hidden = False
If rng1.Cells.Count = Rng2.Cells.Count Then
'do nothing
Else
For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column - 1
If Application.CountA(Rng2.Offset(, i)) = 0 Then
Columns(i + 1).Hidden = True
End If
Next
End If
Application.ScreenUpdating = True
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Actually I was looking for the second answer. However, thanks very much and very grateful for both your helps.
Posting Permissions
Reply With Quote
