Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Set width of cells
I have a sheet with days of the week in the heading, for a month. I would
like to hide the col if it is a Saturday or Sunday. I am working on trying to write a macro to check the value of B3 to B34 (which contains the date and see if it contains Sat or Sun and then set the col width to zero. Thats the theory, but am stuck with the code required to get the check each cell then set the width Thamnks Derrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Set width of cells
' ======================
Option Explicit Sub Check() Dim rng, c Set rng = Range("B3:B34") For Each c In rng If c = "Sat" Then Columns("B:B").ColumnWidth = 0 End If Next End Sub ' ====================== Goodluck -- arunkhemlai "Derk" wrote: I have a sheet with days of the week in the heading, for a month. I would like to hide the col if it is a Saturday or Sunday. I am working on trying to write a macro to check the value of B3 to B34 (which contains the date and see if it contains Sat or Sun and then set the col width to zero. Thats the theory, but am stuck with the code required to get the check each cell then set the width Thamnks Derrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Set width of cells
Hi Derk,
Have a go with the code below. Regards John Sub WeekendHide() Dim iRow As Long Dim iCol As Long If MsgBox("Is currently selected cell at beginning of month?", _ vbYesNo + vbQuestion, _ "Check start") = vbNo Then Exit Sub iCol = ActiveCell.Column iRow = ActiveCell.Row Do Until iCol = 31 Debug.Print iCol If Cells(iRow, iCol).Value = "Saturday" Or _ Cells(iRow, iCol).Value = "Sunday" Then Columns(iCol).Hidden = True End If iCol = iCol + 1 Loop MsgBox ("Weekend colums hidden.") End Sub "Derk" wrote in message ... I have a sheet with days of the week in the heading, for a month. I would like to hide the col if it is a Saturday or Sunday. I am working on trying to write a macro to check the value of B3 to B34 (which contains the date and see if it contains Sat or Sun and then set the col width to zero. Thats the theory, but am stuck with the code required to get the check each cell then set the width Thamnks Derrick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Set width of cells
Made a bit if a mess of explainin my problem, I'll try again
I have 31 colums B2 to AF2, which contain dates (01/01/2005, 02/01/2005, etc). I wish to hide cols that refer to Saturday or Sunday. I undertand the code below, but the line Colums("B:B").Columnwidth=0 needs to relate to each column Thanks "arunkhemlai" wrote in message ... ' ====================== Option Explicit Sub Check() Dim rng, c Set rng = Range("B3:B34") For Each c In rng If c = "Sat" Then Columns("B:B").ColumnWidth = 0 End If Next End Sub ' ====================== Goodluck -- arunkhemlai "Derk" wrote: I have a sheet with days of the week in the heading, for a month. I would like to hide the col if it is a Saturday or Sunday. I am working on trying to write a macro to check the value of B3 to B34 (which contains the date and see if it contains Sat or Sun and then set the col width to zero. Thats the theory, but am stuck with the code required to get the check each cell then set the width Thamnks Derrick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Set width of cells
How about this then?
John Sub WeekendHide() Dim iRow As Long Dim iCol As Long Dim crtVal As Date If MsgBox("Is currently selected cell at beginning of month?", _ vbYesNo + vbQuestion, _ "Check start") = vbNo Then Exit Sub iCol = ActiveCell.Column iRow = ActiveCell.Row Do Until iCol = 33 crtVal = Cells(iRow, iCol).Value If Weekday(crtVal) = vbSunday Or Weekday(crtVal) = vbSaturday Then Columns(iCol).Hidden = True End If iCol = iCol + 1 Loop MsgBox ("Weekend columns hidden.") End Sub "Derk" wrote in message ... Made a bit if a mess of explainin my problem, I'll try again I have 31 colums B2 to AF2, which contain dates (01/01/2005, 02/01/2005, etc). I wish to hide cols that refer to Saturday or Sunday. I undertand the code below, but the line Colums("B:B").Columnwidth=0 needs to relate to each column Thanks "arunkhemlai" wrote in message ... ' ====================== Option Explicit Sub Check() Dim rng, c Set rng = Range("B3:B34") For Each c In rng If c = "Sat" Then Columns("B:B").ColumnWidth = 0 End If Next End Sub ' ====================== Goodluck -- arunkhemlai "Derk" wrote: I have a sheet with days of the week in the heading, for a month. I would like to hide the col if it is a Saturday or Sunday. I am working on trying to write a macro to check the value of B3 to B34 (which contains the date and see if it contains Sat or Sun and then set the col width to zero. Thats the theory, but am stuck with the code required to get the check each cell then set the width Thamnks Derrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ASC() does not convert full-width to half-width letters (Exel 2003 | Excel Worksheet Functions | |||
Macro for column width | Excel Discussion (Misc queries) | |||
HOW DO I CHANGE THE WIDTH OF ONLY A FEW CELLS | Excel Discussion (Misc queries) | |||
How do I change the width of some cells but not others? | Excel Worksheet Functions | |||
Getting Column width using macro.. | Excel Programming |