|
Macro to add / delete column
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.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = 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 |
Macro to add / delete column
Try the below
Sub PEc_General() Application.ScreenUpdating = False Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True If Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n Then Sheets("sheet1").Columns("I:I").EntireColumn.Inser t Shift:=xlToRight Else Sheets("sheet1").Columns("I:I").EntireColumn.Delet e End If Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "TooN" wrote: 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.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = 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 |
Macro to add / delete column
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 -- Regards Dave Hawley www.ozgrid.com "Jacob Skaria" wrote in message ... Try the below Sub PEc_General() Application.ScreenUpdating = False Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True If Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n Then Sheets("sheet1").Columns("I:I").EntireColumn.Inser t Shift:=xlToRight Else Sheets("sheet1").Columns("I:I").EntireColumn.Delet e End If Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "TooN" wrote: 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.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = 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 |
Macro to add / delete column
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. "ozgrid.com" wrote: 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 -- Regards Dave Hawley www.ozgrid.com "Jacob Skaria" wrote in message ... Try the below Sub PEc_General() Application.ScreenUpdating = False Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True If Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n Then Sheets("sheet1").Columns("I:I").EntireColumn.Inser t Shift:=xlToRight Else Sheets("sheet1").Columns("I:I").EntireColumn.Delet e End If Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "TooN" wrote: 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.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = 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 . |
Macro to add / delete column
Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True = Not _
Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True I think the above can be replaced with this much simpler construction. Sheet1.Columns("D:E").Hidden = Not Sheet1.Columns("D:E").Hidden -- Rick (MVP - Excel) "ozgrid.com" wrote in message ... 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 -- Regards Dave Hawley www.ozgrid.com "Jacob Skaria" wrote in message ... Try the below Sub PEc_General() Application.ScreenUpdating = False Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True If Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n Then Sheets("sheet1").Columns("I:I").EntireColumn.Inser t Shift:=xlToRight Else Sheets("sheet1").Columns("I:I").EntireColumn.Delet e End If Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "TooN" wrote: 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.Hidde n = True = Not _ Sheets("sheet1").Columns("D:E").EntireColumn.Hidde n = True Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = True = Not _ Sheets("sheet2").Columns("C:C").EntireColumn.Hidde n = 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 |
Macro to add / delete column
|
| All times are GMT +1. The time now is 06:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com