| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add / delete column
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting a “Delete Column” Macro to a “Delete Row” Macro | Excel Programming | |||
| Macro to delete Column | Excel Discussion (Misc queries) | |||
| macro to delete the last value in each column | Excel Worksheet Functions | |||
| Macro to delete row if value in column J =0 | Excel Programming | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Hybrid Mode
