Hiding blank columns when autofiltered?
 

Hi,

I'm a teacher and have a mark book which I filter by class. I fill in data
in columns, but not every column necessarily, however, every column does have
a date in row 1.

Is there a way to use autofilter to just select the class, but then only
show columns with data in, and hide the rest. Obviously the date being in row
1 of every column might affect this??

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

Paul.

Hiding blank columns when autofiltered?
 

After your autofilter this might be what you are looking for as long as A2
or another particular cell will always be filled on the active columns.

Sub test()
Range("A1").Activate ' or whichever column holds the class names.
Selection.AutoFilter Field:=1, Criteria1:="ClassName"

ActiveSheet.Range("A2").Activate
For x = 1 To 100

If ActiveCell = "" Then
Selection.EntireColumn.Hidden = True
Else
ActiveCell.Offset(0, 1).Activate
End If
If x 100 Then
Exit Sub
End If
Next x
End Sub


Columns("A:Z").select
Selection.EntireColumn.Hidden = False
' to make them visible for the next procedure.
--
Best wishes,

Jim


"PG Oriel" wrote:

Hi,

I'm a teacher and have a mark book which I filter by class. I fill in data
in columns, but not every column necessarily, however, every column does have
a date in row 1.

Is there a way to use autofilter to just select the class, but then only
show columns with data in, and hide the rest. Obviously the date being in row
1 of every column might affect this??

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

Paul.

Hiding blank columns when autofiltered?
 

I have been unable to get the programming below to work. Jim, could I email
you the spreadsheet so you could see it?? Thanks!!

"Jim Jackson" wrote:

After your autofilter this might be what you are looking for as long as A2
or another particular cell will always be filled on the active columns.

Sub test()
Range("A1").Activate ' or whichever column holds the class names.
Selection.AutoFilter Field:=1, Criteria1:="ClassName"

ActiveSheet.Range("A2").Activate
For x = 1 To 100

If ActiveCell = "" Then
Selection.EntireColumn.Hidden = True
Else
ActiveCell.Offset(0, 1).Activate
End If
If x 100 Then
Exit Sub
End If
Next x
End Sub


Columns("A:Z").select
Selection.EntireColumn.Hidden = False
' to make them visible for the next procedure.
--
Best wishes,

Jim


"PG Oriel" wrote:

Hi,

I'm a teacher and have a mark book which I filter by class. I fill in data
in columns, but not every column necessarily, however, every column does have
a date in row 1.

Is there a way to use autofilter to just select the class, but then only
show columns with data in, and hide the rest. Obviously the date being in row
1 of every column might affect this??

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

Paul.

Hiding blank columns when autofiltered?
 

I would be glad for you to send it so I can see better what you need.
Send it to removing the (nospam) of course.
--
Best wishes,

Jim


"PG Oriel" wrote:

I have been unable to get the programming below to work. Jim, could I email
you the spreadsheet so you could see it?? Thanks!!

"Jim Jackson" wrote:

After your autofilter this might be what you are looking for as long as A2
or another particular cell will always be filled on the active columns.

Sub test()
Range("A1").Activate ' or whichever column holds the class names.
Selection.AutoFilter Field:=1, Criteria1:="ClassName"

ActiveSheet.Range("A2").Activate
For x = 1 To 100

If ActiveCell = "" Then
Selection.EntireColumn.Hidden = True
Else
ActiveCell.Offset(0, 1).Activate
End If
If x 100 Then
Exit Sub
End If
Next x
End Sub


Columns("A:Z").select
Selection.EntireColumn.Hidden = False
' to make them visible for the next procedure.
--
Best wishes,

Jim


"PG Oriel" wrote:

Hi,

I'm a teacher and have a mark book which I filter by class. I fill in data
in columns, but not every column necessarily, however, every column does have
a date in row 1.

Is there a way to use autofilter to just select the class, but then only
show columns with data in, and hide the rest. Obviously the date being in row
1 of every column might affect this??

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

Paul.