Calendar manipulation
 

I have a calendar on an Excel 2003 sheet.

1) Column "A" is invisible for historical or hysterical reasons I can't
remember. The sheet is not frozen. Selecting column "B" and the list of
row numbers and doing Format/Columns/Unhide does nothing. In the
Immediate pane of the Code window, commands such as
columns(1).hidden=false also do nothing. This is unimportant, but I
report it.

2) Each month has 2 columns, the first is headed by a month name
followed by day numbers, the second is free format to hold data.
Currently columns("C:GJ") are hidden. I think I need a macro to unhide 2
columns next to the unhidden columns. e.g.
columns("GI:GJ").hidden=false. I am looking for an economical way to
encode this. My current thinking is a loop to search for a hidden =
false cell and make 2 previous cells hidden=true.

3) I also show this file on my Android mobile phone or cellphone.
cells("GK",1) is =DATE(2015,8,1) with format = "mmm"). On the phone,
this appears as 2103 and sometime later is converted to "Aug". The app
is "Sheets". Can anyone suggest a more efficient way to generate "Aug".
--
Walter Briscoe

Calendar manipulation
 

Hi Walter,

Am Mon, 21 Sep 2015 14:31:10 +0100 schrieb Walter Briscoe:

I have a calendar on an Excel 2003 sheet.

1) Column "A" is invisible for historical or hysterical reasons I can't
remember. The sheet is not frozen. Selecting column "B" and the list of
row numbers and doing Format/Columns/Unhide does nothing. In the
Immediate pane of the Code window, commands such as
columns(1).hidden=false also do nothing. This is unimportant, but I
report it.

2) Each month has 2 columns, the first is headed by a month name
followed by day numbers, the second is free format to hold data.
Currently columns("C:GJ") are hidden. I think I need a macro to unhide 2
columns next to the unhidden columns. e.g.
columns("GI:GJ").hidden=false. I am looking for an economical way to
encode this. My current thinking is a loop to search for a hidden =
false cell and make 2 previous cells hidden=true.

3) I also show this file on my Android mobile phone or cellphone.
cells("GK",1) is =DATE(2015,8,1) with format = "mmm"). On the phone,
this appears as 2103 and sometime later is converted to "Aug". The app
is "Sheets". Can anyone suggest a more efficient way to generate "Aug".

1) Put the cursor over the left column separator of column B until it
changes to a double arrow and drag it to the right.

2) Are other columns hidden than A and C:GJ? If not then try following
code to unhide the last two hidden columns:

Sub Test()
Dim RngAdr As String
Dim varRng As Variant

RngAdr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Address
varRng = Split(RngAdr, ",")
varRng = Split(varRng(1), ":")
Range(varRng(0)).Offset(, -2).Resize(1, 2).EntireColumn.Hidden = False
End Sub

3) Another method is =TEXT(DATE(2015,8,1),"MMM")
But I don't know if that is better for your Android mobile


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Calendar manipulation
 

Thanks, Claus. Astounding!

In message of Mon, 21 Sep 2015 16:12:07 in
microsoft.public.excel.programming, Claus Busch <claus_busch@t-
online.de writes
Hi Walter,

Am Mon, 21 Sep 2015 14:31:10 +0100 schrieb Walter Briscoe:

I have a calendar on an Excel 2003 sheet.

1) Column "A" is invisible for historical or hysterical reasons I can't
remember. The sheet is not frozen. Selecting column "B" and the list of
row numbers and doing Format/Columns/Unhide does nothing. In the
Immediate pane of the Code window, commands such as
columns(1).hidden=false also do nothing. This is unimportant, but I
report it.

2) Each month has 2 columns, the first is headed by a month name
followed by day numbers, the second is free format to hold data.
Currently columns("C:GJ") are hidden. I think I need a macro to unhide 2
columns next to the unhidden columns. e.g.
columns("GI:GJ").hidden=false. I am looking for an economical way to
encode this. My current thinking is a loop to search for a hidden =
false cell and make 2 previous cells hidden=true.

3) I also show this file on my Android mobile phone or cellphone.
cells("GK",1) is =DATE(2015,8,1) with format = "mmm"). On the phone,
this appears as 2103 and sometime later is converted to "Aug". The app
is "Sheets". Can anyone suggest a more efficient way to generate "Aug".

1) Put the cursor over the left column separator of column B until it
changes to a double arrow and drag it to the right.
It changes to 4 symbols, which, from the left a
a right-pointing arrow, 2 vertical bars and a left-pointing arrow.
When I did as suggested, I found column("A") empty. Deleting it meant
that a pile of macros, dependent on fixed columns, did not work.


2) Are other columns hidden than A and C:GJ? If not then try following
code to unhide the last two hidden columns:
No! Only A and C:GJ are hidden.


Sub Test()
Dim RngAdr As String
Dim varRng As Variant

RngAdr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Address
varRng = Split(RngAdr, ",")
varRng = Split(varRng(1), ":")
Range(varRng(0)).Offset(, -2).Resize(1, 2).EntireColumn.Hidden = False
End Sub

Astounding! The elegance of your solution leaves me stunned.


3) Another method is =TEXT(DATE(2015,8,1),"MMM")
But I don't know if that is better for your Android mobile

I applied that formula, but found it broke other cells.
cells("GK",1) is =DATE(2015,8,1). That can be changed to
=TEXT(DATE(2015,8,1),"MMM") as suggested.
However, cells("GK",2) is =GM1+1 and that fails with the revised formula
as GM1 is not a number any more.
I am happy to live with the problems. ;)
--
Walter Briscoe