PDA

View Full Version : entirecolumn.hidden with autofilter result



alienscript
10-25-2009, 10:28 AM
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!

Bob Phillips
10-25-2009, 10:55 AM
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

mdmackillop
10-25-2009, 11:09 AM
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

alienscript
10-26-2009, 08:24 AM
Actually I was looking for the second answer. However, thanks very much and very grateful for both your helps.