Prev: Change Color for Drawing Line Object
Next: Coloring Alternate lines
From: Kev - Radio Man on 30 May 2010 23:36
Hi,

I am need of some advice please.

I have a spreedsheet (2007) which I designed as an attendence register.
Currently it works well but I want to improve on a few matters.

For the main page I have a column with the date, next column day, then the
next 40 columns are set for the crew employees. These are broken into 4
groups of 10 for each crew (A,B,C,D, Shift workers), however there is
normally only 7 people per crew, the other 3 are blank unless there are extra
enployees to that crew.
Currently I am hiding and showing manually, but I want to be able to do
something via VB.
Should also mention that I link infomation from these cells to other sheets,
so deleting or adding causes problems, thus I want to keep the set of 40
columns.
(I do have another 2 sheets with similar data but for different divisions,
once I see how to do the 1st sheet I can edit it for these extra 2.)

I also have similar issue on another sheet but in this case it is rows, but
the same type of data.

Is there any way that I can accomplish this? Can it be made to see the 1st
or 2nd cell of a column and then hide on that entry, whether blank or a
special charater.

Thank you for any information. Kevin.

If I have missed any information that will help please tell me.
From: Jacob Skaria on 31 May 2010 01:10
Try the below...which will hide all columns (first 42) if row1 is blank...

Sub Macro2()
Dim lngCol As Long

Application.ScreenUpdating = False
For lngCol = 1 To 42
If Cells(1, lngCol) = "" Then Columns(lngCol).Hidden = True
Next
Application.ScreenUpdating = True

End Sub


--
Jacob (MVP - Excel)


"Kev - Radio Man" wrote:

> Hi,
>
> I am need of some advice please.
>
> I have a spreedsheet (2007) which I designed as an attendence register.
> Currently it works well but I want to improve on a few matters.
>
> For the main page I have a column with the date, next column day, then the
> next 40 columns are set for the crew employees. These are broken into 4
> groups of 10 for each crew (A,B,C,D, Shift workers), however there is
> normally only 7 people per crew, the other 3 are blank unless there are extra
> enployees to that crew.
> Currently I am hiding and showing manually, but I want to be able to do
> something via VB.
> Should also mention that I link infomation from these cells to other sheets,
> so deleting or adding causes problems, thus I want to keep the set of 40
> columns.
> (I do have another 2 sheets with similar data but for different divisions,
> once I see how to do the 1st sheet I can edit it for these extra 2.)
>
> I also have similar issue on another sheet but in this case it is rows, but
> the same type of data.
>
> Is there any way that I can accomplish this? Can it be made to see the 1st
> or 2nd cell of a column and then hide on that entry, whether blank or a
> special charater.
>
> Thank you for any information. Kevin.
>
> If I have missed any information that will help please tell me.
From: Kev - Radio Man on 31 May 2010 01:38
Jacob,

Yes this works, Again thanks.
Can you also advise how to make the same macro unhide the same cells?
I think I just need to change the next statement to hide = false???

Kevin.


"Jacob Skaria" wrote:

> Try the below...which will hide all columns (first 42) if row1 is blank...
>
> Sub Macro2()
> Dim lngCol As Long
>
> Application.ScreenUpdating = False
> For lngCol = 1 To 42
> If Cells(1, lngCol) = "" Then Columns(lngCol).Hidden = True
> Next
> Application.ScreenUpdating = True
>
> End Sub
>
>
> --
> Jacob (MVP - Excel)
>
>
> "Kev - Radio Man" wrote:
>
> > Hi,
> >
> > I am need of some advice please.
> >
> > I have a spreedsheet (2007) which I designed as an attendence register.
> > Currently it works well but I want to improve on a few matters.
> >
> > For the main page I have a column with the date, next column day, then the
> > next 40 columns are set for the crew employees. These are broken into 4
> > groups of 10 for each crew (A,B,C,D, Shift workers), however there is
> > normally only 7 people per crew, the other 3 are blank unless there are extra
> > enployees to that crew.
> > Currently I am hiding and showing manually, but I want to be able to do
> > something via VB.
> > Should also mention that I link infomation from these cells to other sheets,
> > so deleting or adding causes problems, thus I want to keep the set of 40
> > columns.
> > (I do have another 2 sheets with similar data but for different divisions,
> > once I see how to do the 1st sheet I can edit it for these extra 2.)
> >
> > I also have similar issue on another sheet but in this case it is rows, but
> > the same type of data.
> >
> > Is there any way that I can accomplish this? Can it be made to see the 1st
> > or 2nd cell of a column and then hide on that entry, whether blank or a
> > special charater.
> >
> > Thank you for any information. Kevin.
> >
> > If I have missed any information that will help please tell me.
From: Jacob Skaria on 31 May 2010 01:43
Yes.

OR you could toggle between hide/unhide using the below code

Sub Macro2()
Dim lngCol As Long

Application.ScreenUpdating = False
For lngCol = 1 To 42
If Cells(1, lngCol) = "" Then Columns(lngCol).Hidden = _
Not Columns(lngCol).Hidden
Next
Application.ScreenUpdating = True

End Sub




--
Jacob (MVP - Excel)


"Kev - Radio Man" wrote:

> Jacob,
>
> Yes this works, Again thanks.
> Can you also advise how to make the same macro unhide the same cells?
> I think I just need to change the next statement to hide = false???
>
> Kevin.
>
>
> "Jacob Skaria" wrote:
>
> > Try the below...which will hide all columns (first 42) if row1 is blank...
> >
> > Sub Macro2()
> > Dim lngCol As Long
> >
> > Application.ScreenUpdating = False
> > For lngCol = 1 To 42
> > If Cells(1, lngCol) = "" Then Columns(lngCol).Hidden = True
> > Next
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> >
> > --
> > Jacob (MVP - Excel)
> >
> >
> > "Kev - Radio Man" wrote:
> >
> > > Hi,
> > >
> > > I am need of some advice please.
> > >
> > > I have a spreedsheet (2007) which I designed as an attendence register.
> > > Currently it works well but I want to improve on a few matters.
> > >
> > > For the main page I have a column with the date, next column day, then the
> > > next 40 columns are set for the crew employees. These are broken into 4
> > > groups of 10 for each crew (A,B,C,D, Shift workers), however there is
> > > normally only 7 people per crew, the other 3 are blank unless there are extra
> > > enployees to that crew.
> > > Currently I am hiding and showing manually, but I want to be able to do
> > > something via VB.
> > > Should also mention that I link infomation from these cells to other sheets,
> > > so deleting or adding causes problems, thus I want to keep the set of 40
> > > columns.
> > > (I do have another 2 sheets with similar data but for different divisions,
> > > once I see how to do the 1st sheet I can edit it for these extra 2.)
> > >
> > > I also have similar issue on another sheet but in this case it is rows, but
> > > the same type of data.
> > >
> > > Is there any way that I can accomplish this? Can it be made to see the 1st
> > > or 2nd cell of a column and then hide on that entry, whether blank or a
> > > special charater.
> > >
> > > Thank you for any information. Kevin.
> > >
> > > If I have missed any information that will help please tell me.
From: Kev - Radio Man on 31 May 2010 01:58
Yes that did the job, again.
Thanks for your help, it sure makes it easier with the lack of coding
knowledge.

Kevin.
High Regards

 | 
Pages: 1
Prev: Change Color for Drawing Line Object
Next: Coloring Alternate lines