Hid and unhide and clear contents for specific columns at the same time

I have a serious problem with excel programming and i am not able to
figureout how to  do the following funtions.

I have 24 columns. names Jan -Dec ( Actual) then Jan- Dec (Forecast).
There is one reference cell A1 ( for current month).
- If  A1= Jan, I want macro to hide FEB-DEC 9 Actual , and leave
FEB-DEC (forecast).
-If A1= FEB , I want macro to unhide FEB Actual and hide MAR-Dec (
actual) and at the same time clear contents in FEB (forecast) and hide
feb (forecast ).

ANd so on


I would really appreciate if somene could help me figure out this
problem. 

Thanks

0
8/22/2006 3:27:42 PM
excel 39879 articles. 2 followers. Follow

1 Replies
317 Views

Similar Articles

[PageSpeed] 22

Use one of these in the sheet module where cell a1 contains the number of 
the month.

Sub hidecols()
Columns.Hidden = False
mc = Range("a1") + 1
Range(Cells(1, mc), Cells(1, 12)).EntireColumn.Hidden = True
End Sub

'Automatic every time you change cell a1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Columns.Hidden = False
mc = Target + 1
Range(Cells(1, mc), Cells(1, 12)).EntireColumn.Hidden = True
End Sub

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<hamad.fatima@gmail.com> wrote in message 
news:1156260462.231698.127570@m73g2000cwd.googlegroups.com...
>I have a serious problem with excel programming and i am not able to
> figureout how to  do the following funtions.
>
> I have 24 columns. names Jan -Dec ( Actual) then Jan- Dec (Forecast).
> There is one reference cell A1 ( for current month).
> - If  A1= Jan, I want macro to hide FEB-DEC 9 Actual , and leave
> FEB-DEC (forecast).
> -If A1= FEB , I want macro to unhide FEB Actual and hide MAR-Dec (
> actual) and at the same time clear contents in FEB (forecast) and hide
> feb (forecast ).
>
> ANd so on
>
>
> I would really appreciate if somene could help me figure out this
> problem.
>
> Thanks
> 


0
dguillett1 (2487)
8/22/2006 4:26:15 PM
Reply:

Similar Artilces:

Edit a column of names
Hi I have a worksheet with about 300 names in a column The names are laid out like Adrain Mr R. or Buchanan Mr & Mrs T. etc etc Is there some way to remove the titles and just leave the Surname without have to do it a line at-a-time? Martin ���� Martin, In your worksheet, make sure you have blank columns to the right of the column that contains the names. Highlight the names. Click the "Data" menu item. Click "Text to Columns...". In the pup-up wizard, select the type of data as "Delimited". Click the "Next" button. Remove the check mark fro...

How do I specify column for named range of rows
I have a sheet on which new lines of data get added almost daily, the first column being a time and date stamp. To keep things organized, I name ranges of rows, describing the time interval they contain. For example, the range $10:$500 could be named "June". Now I want to chart different time intervals in a chart only by changing the name in the SERIES function. For example, if I am plotting column B against time, I would want to specify like SERIES(,June A, June B). I know this syntax is not accpeted, but is there any way it can be done, specifying only the column and ro...

Column Report (Right to Left)
Good Evening. I have a report that I have finally managed to arrange such that I can print landscape on the front page and Portrait on the back. I have one problem left.... The back page has a columnar subreport in the lower fifths of the page. The subreport functions fine except i need it to fill from right to left. The data is in the proper order but the report fills from left to right? How can I fix this? Thank You Tirelle ...

Acc2002: Crosstabs: Column header length limit?
Dear all, crosstabs in Access 2002 seem to have a limit for the length of column headers: 20 characters. Queries with headers that differ only after the first 20 characters do not work. Any suggestions to get it work? Is there, e.g., any registry parameter that could be adjusted? Thanks for every hint, JotKa I think you would find the same thing to be true for field names in a table or controls on a form. AFAIK, there is no workaround. Make sure the first 20 characters are unique. -- HTH, George "JotKa" <JotKa@discussions.microsoft.com> wrote in message news:9B12...

printing cell contents
Hi, I need to print the worksheet but i want to print not the values but the formulas that each cell contains. Could you pls tell me how to do it? <Ctrl>+<`> That second key is a reverse apostrophe - found over the Tab key, and to the left of number 1. Then just print. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Kostas" <arko@excite.com> wrote in message news:062501c37871$fba9e980$a001280a@phx.gbl... Hi, I need to print the works...

Pasting into columns...
Hi. I've used Excel 97 for years. One of the things I did frequently was copy info from my bank accounts and paste (special/unformatted text) into a workbook. The data always pasted neatly into my preformatted columns, e.g., a line reading "02/28/2007 Check 2100 $500.00 $1500.00" went into columns headed Date, Transaction, Amount, Balance. Now I've upgraded to Excel 2003 (in Windows Vista) and when I paste the same data it all goes into the first column, meaning I have to cut "Check 2100", "$500.00", and "$1500.00" and paste each indivi...

Eliminate duplicates(values) in an Excel column !!
Hi Guys G'day. I have a long list of email values in a column in excel spreadsheet(windows 2000). I want to findout the duplicates and want to eliminate those. ie. If I have jo@cs.co.au 5 times in an excel column , I just want to keep only one(keep uniqueness). Can anybody help me how I can find the duplicates and eliminate those in an Excel column. In the end I want a column which contains unique email addresses. Thank you. Jo Jo, have a look at data, filter, advanced filter and unique records only and see if this will do what you want -- Paul B Always backup your data before tryin...

Specific cell values
Hi everyone, i need to acheive the following but i'm not sure how to do it. i have a cell (K49), the cell needs to collect specific data from a range ( D7:D32 ) on the same worksheet. it needs to look in the range and if the cell value is CNC, then add the figure form the same row in column E. Example: D E F glass £200 26 August 05 metal £650 26 August 05 wood £300 26 August 05 CNC £1000 ...

How to set up emails with specific domains
Hi all, 1 more question: how do I set up email addresses with specific domains - not the usual @hotmail / @yahoo but company ones made specifically for the company. Regards & thanks Sketch Sketch <sketcher@eircom.net> wrote: > how do I set up email addresses with specific domains - not the usual > @hotmail / @yahoo but company ones made specifically for the company. In Outlook, Tools>E-mail Accounts>Add a new e-mail account. In Outlook Express, Tools>Accounts>Add. -- Brian Tillman You need to consult the documentation for the mail server hosting your =...

Added data in columns
I am new to Excel. I have a project I am working on in which I have a column with a value from 1 to 5. Each number (and in some columns letters) represents a different answer. Is there a way to add the total number of each value? For instance I have over 2000 rows with data. In column "N", an answer can be either 1,2,3,4 or 5. How would I figure out how many answers are "1", how many are "2" an so on. Likewise there is a column with a yes/no answer. How can I see the total number of Yes's and how many No's there are? I appreciate any help. Hi ...

Need a time-date stamp for MS Outlook, please #2
From: "Hugo Acosta" <hacosta@CNYLatino.com> Subject: Need a time-date stamp for MS Outlook, please Date: Friday, September 22, 2006 11:34 AM I apologize in advance if I am sending this request to the wrong place... I would like to have a button in my MS Outlook 2000 toolbar, that when clicked, it will place the DATE/TIME on (whatever) the cursor is located. I work (volunteer) with a non-for-profit Church organization that has no money to pay programmers, to develop (what I was told) this simple task; therefore, I was advised to use this Internet avenue, and see if I could ...

Key in Date and time
How can i code the fields with mm/dd/yyyy hh:mm. so that when i key in i just key in numbers don have to worried about the the slashes and colon. Thanks. -- Tylim ------------------------------------------------------------------------ Tylim's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34217 View this thread: http://www.excelforum.com/showthread.php?threadid=540530 You need code for that http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good ...

Help with looping thru columns
I trying to automate a report that is ran in Cognos, and extracted to an excel file. I am almost complete, but am having trouble with totaling coulmuns. Basically, There are 7 columns which are to be totaled. My limitation is this: IF column 'D' is = "G" then loop thru Rows and Columns to process G Totals Then IF column 'D' is = "N" then loop thru Rows & Columns to Process N Totals Then Total the Totals for "G" and "N"; Example: A B C D E F G H I J Total 2915R NOYR 131000 G 0.00 (470.28) 0.00...

Can you perform ctrl+shift+; in a macro to display time?
I would like to add current but static time to a cell within a larger macro, but the only way I can find to enter just the time and not date as well is to use ctrl+shift+; but it doesn't appear that macro allow the use of hardkeys, although my knowledge of macros is basic i'm sure there must be a work around. Hi Darran Try: '=============>> Public Sub TesterC() With Range("A1") .Value = Time .NumberFormat = "h:mm AM/PM" End With End Sub '<<============= --- Regards, Norman "Darran Parsons" <Darran...

Clear Cell Format
Some time ago, from a post by JE McGimpsey, I learned the following macro, which will toggle the substitution of "." with ":" However it left the cell in Time Format after its initial entry. How do I clear the Time Format, when decimal point is back to normal state. Please, Public Sub ToggleDotTime() Dim strmsg As String strmsg = "Decimal Point is NORMAL" With Application.AutoCorrect On Error Resume Next .DeleteReplacement (".") If Err Then .AddReplacement ".", &...

Lookup column
Hello Everyone, I have a lookup column in my database with two parts. One part lists the supplier's name with the supplier's ID number directly accross in the other part. i enter data via a form. What I would like to do is select the supplier field on the form but have the supplier's ID number auto populate in another field on the form. Is this possible or am I going about this the wrong way? Thanks. If by "lookup column" you mean a combo box, and if both the supplier name and supplier ID appear in the combo box list, then you can use the column method to accomp...

Move columns in datasheet view
In Access 2003, I'm finding that I can't move columns in a form in datasheet view mode. I was able to do this in Access 97, and Help seems to show similar instructions for moving the columns. I don't see the pointer change to a rectangle as it did in AC97. The columns aren't frozen. Is this a bug? Thanks. -- David Jones Hi David, I just tried it on a new form in Access 2003 and I could move the columns. Jeanette Cunningham "d381e" <d381e@lycos.com> wrote in message news:47618A68.6080803@lycos.com... > In Access 2003, I'm finding that I can'...

how do I change time data to numerical result
I am trying to create a roster spread sheet to calculate amount of hours and cost. I would like to know how to change my result from formula. i.e. 8h:30min to 8.5 hours? If your data is truly in the format of hh:mm, then simply multiply by 24, and format the cell as number or General. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "J. Gorman" <J. Gorman@discussions.microsoft.com> wrot...

Stacked Column Chart...HELP NEEDED
I would like to stack 2 sets of data side by side for each month. For example, I have Category A (type 1, type 2) and Category B (type 1, type 2). I am using a stacked column chart and cannot seem to do this without jumbling around the data section which doesn't make for easy understanding. Different chart type? Any ideas? If you stagger your data, you can create side-by-side stacked columns. Bernard Liengme has an example and instructions on his site: http://www.stfx.ca/people/bliengme/ExcelTips/Columns.htm and the following MSKB article has an example: XL200...

Compare two columns datas
Hi, Lookin for an easy way to tell TRUE if two columns values are EXACTLY the same (same row, same value) and FALSE if not... Is there an Excel function to do that ? Or will I have to check every cell (if A1=B1, A2= B2,....) thks for help. =SUMPRODUCT(--(A1:A100<>""),--(A1:A100=B1:B100))=MAX(COUNTA(A1:A100),COUNTA( B1:B100)) -- HTH RP (remove nothere from the email address if mailing direct) "Franck" <wesley.saris@gmail.com> wrote in message news:1113814052.562556.195190@f14g2000cwb.googlegroups.com... > Hi, > Lookin for an easy way to tell TRUE ...

Is there a Date Time Conversion from text?
I have DateTime stored as text in an Access db field. Ex: 1/22/2008 10:34:29 AM I want to convert this data form text to a real DateTime value all inclusive. I am able to pull the date DateValue and time TimeValue, but is there a way to get them both together. Thanks Try using CDate or DateAdd DateAdd("D",0,[Your Text Date Time]) CDate([Your text date time]) If your string field could be null, or a zero-length string, or invalid, then I suggest you test it first IIF(IsDate([Your Text Date Time]),CDate([Your Text Date Time]),Null) -- John Spencer Access MVP 2002-2005, 2...

Help, insert a word document contents into excel tab?
I need some help. I have a word document which is my expense report procedure. Then I have an excel expense report. Now the boss says it sure would be nice if I put the procedure on a tab in the excel document so that everyone that has an expense report will have the procedure. Now that doesn't seem tough. I'm inserting an object, browsing to the word document, and inserting. I did not pick link because we want it to stand alone once I get it all into excel. Everything looks good but I only end up with the 1st page of my word document in the excel tab. How do I get the whole documen...

Get Excel to accept negative values using time-format
Hello Excel seems to refuse negative values in cells that are having i time-format. Is there a way to solve this? (Yes, time can be negative. If you for example compare planned time with actual time, the difference can be positive or negative) Kind regards Marcus Hi Marcus Excel will not display negative time using the standard 1900 date system. It displays a series of #########'s. However, the result can be used in further calculations. If you switch to the 1904 date system, Tools>Options>Calculation>click 1904 dates. Beware of other changes to dates already entered as ...

Audit Public folder contents
Hi Is there anyway of tracking public folder item deletions? I know I can enable the medium logging in diagnostic logging but that only reports on deletions of the folder not the items within the folder. We have a few calendars and entries keep getting deleted. Please let me know if you have any ideas. Thanks Tec Have you considered removing the permissions from users to delete "all items". If you change the deleted items setting to “own” this might resolve your issue. "Tecnik" wrote: > Hi > > Is there anyway of tracking public folder item deletion...

Convert a number to a date time via unbound feilds
Hi, I have two unbound fields, called runDate and runTime on my form. I extract the run date and run time from the RecID field (which is the primary key). This primary key is created by a script that outside of my DB (i.e. I import the data). I can extract the date and time which look like this: 24012010 = 24/01/2010 235336 = 23:53:36 Now I would like to convert these numbers to their respective formats (i.e. 24/01/2010 and 23:53:36) and would like these fields to update whenever the record changes... I have used the following code at the DataChange event of the for...