How to Show/Hide Columns or Rows with no data via VB

[Kev - Radio Man]

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.

 

[Jacob Skaria]

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

 

[Kev - Radio Man]

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]

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

 

[Kev - Radio Man]

Yes that did the job, again.
Thanks for your help, it sure makes it easier with the lack of coding
knowledge.

Kevin.
High Regards