Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto hiding blank rows and columns | Excel Discussion (Misc queries) | |||
Reverting all Autofiltered columns to "ALL" | Excel Discussion (Misc queries) | |||
Hiding blank rows | Excel Programming | |||
Remove blank rows without hiding them | Excel Programming | |||
Hiding blank rows | Excel Discussion (Misc queries) |