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

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.

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

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.

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

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.

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

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.

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

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

Kevin.
High Regards