Remove the single apostrophe from in front of each line of code.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"PG Oriel"
wrote in message
Hi, I have the following code which is meant to hide columns where there is
no data in certain columns. It used to auto update but it stopped working so
I tried attaching it to a button but it too has stopped working. Any idea
why? The system works by have pupils names in column B, class in column C and
levels of behaviour on column C onwards, starting in row B for my first set.
I want it to autofilter and then hide columns where there is no information
for any column from C onwards for a class. eg. Column D and Column F might
be empty for Class 7P which occupy rows 37-65 etc.

-snip-

'Private Sub Worksheet_Calculate()
'If Me.AutoFilterMode Then
'ABC
'Else
'Columns.Hidden = False
'End If
'End Sub

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Me.AutoFilterMode Then
'ABC
'Else
'Columns.Hidden = False
'End If
'End Sub

I added a three message boxes to your code.
Run it and see if you can reconcile what they tell you with
what you see or are expecting...
'-------------
Sub ABC()
Dim lastColumn As Long, rngA As Range, i As Long
Dim rng1 As Range
'Application.ScreenUpdating = False
Columns.Hidden = False
lastColumn = Me.Cells(2, "IV").End(xlToLeft).Column
Set rngA = Me.AutoFilter.Range
Set rngA = rngA.Offset(1, 0).Resize(rngA.Rows.Count - 1, 1)
MsgBox "AutoFilter range is " & rngA.Address
For i = 4 To lastColumn
Set rng1 = Cells(2, i).Resize(rngA.Rows.Count, 1)
MsgBox "Checking totals for range " & rng1.Address
If Application.Subtotal(2, rng1) = 0 Then
MsgBox rng1.Address & " has no total and wiil be hidden. "
Columns(i).Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"PG Oriel"
wrote in message
Hi, I have done this, saved it and restarted the xls sheet and it still
doesn't work. It seems to work up to column X Y and Z which aren't hiding,
despite being blank and there being information in column AA.

"Jim Cone" wrote:
Remove the single apostrophe from in front of each line of code.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"PG Oriel"
wrote in message
Hi, I have the following code which is meant to hide columns where there is
no data in certain columns. It used to auto update but it stopped working so
I tried attaching it to a button but it too has stopped working. Any idea
why? The system works by have pupils names in column B, class in column C and
levels of behaviour on column C onwards, starting in row B for my first set.
I want it to autofilter and then hide columns where there is no information
for any column from C onwards for a class. eg. Column D and Column F might
be empty for Class 7P which occupy rows 37-65 etc.

-snip-

'Private Sub Worksheet_Calculate()
'If Me.AutoFilterMode Then
'ABC
'Else
'Columns.Hidden = False
'End If
'End Sub

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Me.AutoFilterMode Then
'ABC
'Else
'Columns.Hidden = False
'End If
'End Sub

Hi Jim,

It was working a treat and I could see it hiding the columns. It then got to
column X and stopped. Therefore columns Y, Z, and AA which also need hiding
haven't been hidden. If you know why the help is greatly appreciated.

Thanks.
Paul

"Jim Cone" wrote:

I added a three message boxes to your code.
Run it and see if you can reconcile what they tell you with
what you see or are expecting...
'-------------
Sub ABC()
Dim lastColumn As Long, rngA As Range, i As Long
Dim rng1 As Range
'Application.ScreenUpdating = False
Columns.Hidden = False
lastColumn = Me.Cells(2, "IV").End(xlToLeft).Column
Set rngA = Me.AutoFilter.Range
Set rngA = rngA.Offset(1, 0).Resize(rngA.Rows.Count - 1, 1)
MsgBox "AutoFilter range is " & rngA.Address
For i = 4 To lastColumn
Set rng1 = Cells(2, i).Resize(rngA.Rows.Count, 1)
MsgBox "Checking totals for range " & rng1.Address
If Application.Subtotal(2, rng1) = 0 Then
MsgBox rng1.Address & " has no total and wiil be hidden. "
Columns(i).Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"PG Oriel"
wrote in message
Hi, I have done this, saved it and restarted the xls sheet and it still
doesn't work. It seems to work up to column X Y and Z which aren't hiding,
despite being blank and there being information in column AA.

"Jim Cone" wrote:
Remove the single apostrophe from in front of each line of code.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"PG Oriel"
wrote in message
Hi, I have the following code which is meant to hide columns where there is
no data in certain columns. It used to auto update but it stopped working so
I tried attaching it to a button but it too has stopped working. Any idea
why? The system works by have pupils names in column B, class in column C and
levels of behaviour on column C onwards, starting in row B for my first set.
I want it to autofilter and then hide columns where there is no information
for any column from C onwards for a class. eg. Column D and Column F might
be empty for Class 7P which occupy rows 37-65 etc.

-snip-

'Private Sub Worksheet_Calculate()
'If Me.AutoFilterMode Then
'ABC
'Else
'Columns.Hidden = False
'End If
'End Sub

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Me.AutoFilterMode Then
'ABC
'Else
'Columns.Hidden = False
'End If
'End Sub