Macro to add / delete column

[TooN]

Hello,

I dont think this is difficult but i cannot get it work properly. I have the
following macro:

Sub PEc_General()
Sheets("sheet1").Columns("D:E").EntireColumn.Hidden = True = Not _
Sheets("sheet1").Columns("D:E").EntireColumn.Hidden = True
Sheets("sheet2").Columns("C:C").EntireColumn.Hidden = True = Not _
Sheets("sheet2").Columns("C:C").EntireColumn.Hidden = True
End Sub

This macro toggles between hiding and showing some columns. Macro works
perfect but i want to extend it a little bit. If i activate the macro it
hides the columns (D,E and C), if i activate it again it shows the columns
again. What i would like is the following. If i activate the macro it hides
the columns and i would like to create an extra column
(Columns("I:I").EntireColumn.Insert Shift:=xlToRight). If i activate the
macro again it shows the columns again and i would like to delete the created
column again (column I)

Hopefully i explained it correct... Thanks

 

[Jacob Skaria]

Try the below

Sub PEc_General()
Application.ScreenUpdating = False
Sheets("sheet1").Columns("D:E").EntireColumn.Hidden = True = Not _
Sheets("sheet1").Columns("D:E").EntireColumn.Hidden = True
Sheets("sheet2").Columns("C:C").EntireColumn.Hidden = True = Not _
Sheets("sheet2").Columns("C:C").EntireColumn.Hidden = True

If Sheets("sheet1").Columns("D:E").EntireColumn.Hidden Then
Sheets("sheet1").Columns("I:I").EntireColumn.Insert Shift:=xlToRight
Else
Sheets("sheet1").Columns("I:I").EntireColumn.Delete
End If
Application.ScreenUpdating = True

End Sub

 

[ozgrid.com]

You can shorten your code, and make it more robust, if you use

Sheet1.Range("D:E")....

where Sheet1 is the CodeName
http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[Kev - Radio Man]

Hi, to add to this question, is it possible to make the macro hide/unhide
columns which have no data in them or maybe a unique entry in the top cell
indicating this column can be hidden?
I am running a set number of columns as a shift roster (10 columns wide for
each of 4 crews, total 40 columns), but the columns are not always full with
employees (currently 7 per crew).
A quick way to hide the empty columns and then unhide when I have to add a
new employee, or remove.

Regards Kevin.

 

[Rick Rothstein]

Sheets("sheet1").Columns("D:E").EntireColumn.Hidden = True = Not _
I think the above can be replaced with this much simpler construction.

Sheet1.Columns("D:E").Hidden = Not Sheet1.Columns("D:E").Hidden

 

[Abhi]

Respected Sir/Madam

Please send me your Excel Sheet. My Personal Id is

[email protected]

Thanks