Hiding columns whilst autofiltered - PART 2 HELP!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm a teacher and have a mark book which I filter by class (Column C, rows 2
down) . I fill in data in other columns, but not every column necessarily,
however, every column does have a date in row 1, and lesson objective in row
2.

Is there a way to use autofilter to just select the class, but then only
show columns with data in, and hide the rest.

For instance, when I filter one class, only rows 29 to 61 are on show. I
then have data in columns F and G, whilst D and E are blank for this class.
I'd like these columns to be hidden for this class, and if I selected another
class, then whatever columns are blank for them to be hidden.

If you do have any hints, that'd be fantastic!!!

Paul.
 
Sub ABC()
Dim lastColumn As Long, rngA As Range, i As Long
Dim rng1 As Range
Columns.Hidden = False
lastColumn = Cells(1, "IV").End(xlToLeft).Column
Set rngA = ActiveSheet.AutoFilter.Range
Set rngA = rngA.Offset(1, 0) _
.Resize(rngA.Rows.Count - 1, 1)
For i = 4 To lastColumn
Set rng1 = Cells(2, i).Resize(rngA.Rows.Count, 1)
If Application.Subtotal(3, rng1) = 0 Then
Columns(i).Hidden = True
End If
Next
End Sub
 
Back
Top