hiding columns automatically #2

I have a number of columns on a spreadsheet that need not be viewed or 
printed if a given field within that range is zero. I am lloking for syntax 
that will automatically hide (say) columns a to f where the value in (say) 
c20 is less than 1?

I have  macro that closes the columns if I select them manually but given 
volumes need to do this automatically.

Any ideas


Thanks
0
daveglynn (29)
2/1/2005 6:15:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
541 Views

Similar Articles

[PageSpeed] 9

Something like this perhaps? You will have to assign it to a button or run it 
from the macro dialog. 

Sub HideAtoF()
    If Range("C20").Value < 1 _
    Then Range("A:F").Columns.EntireColumn.Hidden = True
End Sub

tj

"dave glynn" wrote:

> I have a number of columns on a spreadsheet that need not be viewed or 
> printed if a given field within that range is zero. I am lloking for syntax 
> that will automatically hide (say) columns a to f where the value in (say) 
> c20 is less than 1?
> 
> I have  macro that closes the columns if I select them manually but given 
> volumes need to do this automatically.
> 
> Any ideas
> 
> 
> Thanks
0
tjtjjtjt (488)
2/1/2005 6:27:02 PM
hi,
Private Sub Worksheet_SelectionChange(ByVal Target As 
Range)

    If Range("C20").Value = 0 Then
        Columns("A:F").EntireColumn.Hidden = True
        Else
        Columns("A:F").EntireColumn.Hidden = False
    End If
    
End Sub

>-----Original Message-----
>I have a number of columns on a spreadsheet that need not 
be viewed or 
>printed if a given field within that range is zero. I am 
lloking for syntax 
>that will automatically hide (say) columns a to f where 
the value in (say) 
>c20 is less than 1?
>
>I have  macro that closes the columns if I select them 
manually but given 
>volumes need to do this automatically.
>
>Any ideas
>
>
>Thanks
>.
>
0
anonymous (74723)
2/1/2005 6:36:54 PM
Reply:

Similar Artilces:

outlook 2000 automatically changing e-mail address
how can I prevent Outlook 2000 to automatically replace a manually entered e-mail address for a contact with the first entry. For example: For contact "jim" I have an e-mail address "jim@abc" and an email 2 address of "jim@xyz". I want a specific message to go to the email 2 address and manually enter "jim@xyz" in the To field. When Outlook sends the message, it replaces the "jim@xyz" with "jim@abc" and sends the message to that last account. ...

summing a column according to criteria of two other columns
I am creating a cash flow forecast sheet and trying to add up the project values of a particular service provided in june this year. i am attempting to do this by referring to a sheet called data which has a column of project values (N), a column with the month and year of the project (V) written as 62004 and a column of services (E) of which i want to select those projects where the service was Translation - Standard. By looking at the help for countif by multiple criteria i altered the formula to a sumif. I am using the formula... =SUM(IF((Data!$E$3:$E$2000='Translation - Standard'...

setting an assumption #2
What is an assumption and how do i set an assumption? I'm taking an excel course in school and i just wanted some clearafication on assumptions. Thank you in advance for any help. First it is unwise to assume that your first post failed. Secondly, please give some example of what you are looking for ? I am not going to make any assumptions yet. Steve On Tue, 31 Oct 2006 20:36:01 -0000, beth <beth@discussions.microsoft.com= > = wrote: > What is an assumption and how do i set an assumption? I'm taking an ex= cel > course in school and i just wanted some clearaficatio...

Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date, StockLocation and NumberOnStock. When I make simple Pivot Table with ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock" I would like to hide rows where the sum is "0". But this does not seem to be possible. Tried to follow the guidelines in the Microsoft article: http://office.microsoft.com/en-us/assistance/HP051998791033.aspx#Hide%20zero%20values%20in%20a%20PivotTable%20report But no luck :-( Mogens You could use conditional formatting on these cells - highlight the cells,...

Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without protecting the sheet. I am making something for other teachers at my school and I want to hide the formulas so they won't mess anything up on the spreadsheet. Any help would be greatly appreciated. Thanks! Hi not without protecting the sheet but why is this a problem for you? -- Regards Frank Kabel Frankfurt, Germany "JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag news:ABFE07BF-6087-4E24-814A-03AF8863A633@microsoft.com... > Hi. I was wondering if there was a way to hide the fo...

Not allowing Alpha Characters in a Numeric Formatted Column
I have a spreadsheet that we provide to vendors where they have to fill in their product data and send it back to us where we import this data into the products database. I have the spreadsheet set up the way I want it and have drop down boxes where applicable for data validation. I have several fields that i only want #'s. Sometimes integers, sometimes there may be a decimal. I set the formats accordingly. HOwever, you can still type the letter A or B or C etc... in there. I only want them to be able to type in #'s. In access I can use a mask. Is there anything similar in Exce...

display columns alphabetically
Columns used to display alphabetically left to right. It changed to now displaying numerically left to right. How do I change back to alphabetically? Go to Tools|Options and then in the General tab, uncheck R1C1 reference style. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=136818 Perfect, thanks for easing my f...

Automatically Highlight Every Second Row in Excel
Hi, I would like to know if there is any way to have excel automatically highlight every second row in a sheet to make it easier to read accross the row? Thanks GW Can you use conditional formatting? If yes, see Chip Pearson's site: http://www.cpearson.com/excel/banding.htm NHB wrote: > > Hi, > > I would like to know if there is any way to have excel automatically > highlight every second row in a sheet to make it easier to read accross the > row? > > Thanks > GW -- Dave Peterson ec35720@netscape.com ...

hide my name
When replying to an email or creating one from scratch how do i hide my name in the "from" field...thx fred <fdbjrgetridofthis@wowway.com> wrote: > When replying to an email or creating one from scratch how do i hide > my name in the "from" field...thx Are you saying you want the reply to be anonymous? -- Brian Tillman [MVP-Outlook] no, I don't mind if my email is revealed.. just my name. I have tried by removing it from the "User Name:" field in the email account but it continues to show up. I have also removed from the header informati...

XML Serialization bug? #2
Take a class: public class AClass { public bool FurtherActionsRequired; public bool FurtherActionsRequiredSpecified; } this class serialized to XML produces the following XML output: <?xml version="1.0" encoding="utf-8"?> <AClass xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <FurtherActionsRequiredSpecified>false</FurtherActionsRequiredSpecified> </AClass> Why does FurtherActionsRequired not get serialized to XML? Is this a bug? Adam Adam, Was my last ex...

Convert Column Letters to Number
Hi! I'm looking for a function to convert a "large" column letters (up t 10 characters) to it's equivalent in number. e.g. Input=A Output=1 Input=AAA Output=703 Input=EXCEL Output=2,708,874 Any suggestions? Regards -- Message posted from http://www.ExcelForum.com Here's a VBA function that gives the results you want: Option Explicit Function ColumnToNumber(sText As String) As Variant Dim Bytes() As Byte Dim Letter As Long Dim Multiplier As Double Dim N As Double Dim Total As Double ColumnToNumber = CVErr(xlErrValue) If L...

Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then having it run through a query to filter results. Is there a way that I can have certain feilds hidden until a toggle button or something of the like is clicked? Basically I want have three choices of buttons: Year, Quarter, and Month. What I ideally want to happen is when someone selects the button for year, a hidden drop down menu appears where they can then choose from the available years to filter through the query. I would say I am above average with using access, but I have no idea how to hide fields and then...

How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select data > hidden and empty cell button does not give an option to hide gaps. I think by default they are hidden but if you even click on this to see what it does the gaps are shown and no way to turn off. Hi, If you hide the actual rows then with the Plot visible cells only setting on the gaps will be removed. In xl2007 the setting is on the Select Data dialog, Hidden/empty cells button. Cheers Andy Waynesch wrote: > I have a bar chart that is showing gaps and i don't want them. The s...

Cannot add columns to custom views
I have a problem that is causing a major handicap. I cannot add any columns to any custom leads view that I create. No matter what, the only column that is displayed is the default key column (name) that is there when the view is created. When I click on add columns, the dialog box that is supposed to list columns that I can add to the view has no columns listed at all. I am logged in as the System Administrator when trying to accomplish this. Also, when I create a filter criteria it is never saved. I save it, but when I go back into it, it is gone. Any help would be GREATLY apprec...

Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it. Hi is your worksheet protected ? -- Regards Frank Kabel Frankfurt, Germany llong wrote: > I want to hide a worksheet. So, I went to the format menu and the > "worksheet" option is grayed out. Why? And, how do I "ungray" it. I think Frank meant to ask about the Workbook--not the worksheet. (Check under Tools|protection|Protect workbook or Unprotect workbook) My question is: Is there another sheet in the w...

VBA write macro change column with 3 number digits to 4 digits the
-- James Do you mean Sub Macro() Range("D1:D10").NumberFormat = "0000" End Sub -- Jacob "James C" wrote: > > -- > James Use the white space (here) to record details of your requirements - don't try to put it all in the message header. Pete On Jan 25, 1:51=A0pm, James C <Jam...@discussions.microsoft.com> wrote: > -- > James Thanks for your response -- James "Jacob Skaria" wrote: > Do you mean > > Sub Macro() > Range("D1:D10").NumberFormat = "00...

charting calculated values #2
The cells for future months contain formulas which give values of zer until data is entered on other linked sheets -- dp ----------------------------------------------------------------------- dpj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=149 View this thread: http://www.excelforum.com/showthread.php?threadid=27225 ...

Hide images when Hiding Rows
I have inserted images into cells, but when I hide the column the images are not hidden with that column, they just get squished between the two cells next to the hidden cell. Is there a setting or way to tie the image to that cell so that it hides/unhides appropriately with the row? -- kurt ------------------------------------------------------------------------ kurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35686 View this thread: http://www.excelforum.com/showthread.php?threadid=554712 Kurt You cannot insert an image into a cell. You can overlay ...

Hiding plot Area
I have a formatted output sheet that has a Pie Chart embedded. The data for the pie chart comes from a second sheet using 2 ranges B2:B6 & D2:D6 B2:B6 is always populated and contains labels. What I want to do is, when D2:D6 contains 0 in each cell (or Null), suppress the Pie chart but always show the Legend (which is driven by B2:B6) Many Thanks Adam Adam - All you see of the pie when all values are zero is a single line at the first slice. The legend still appears. To hide the line, double click on one of the wedges and on the Patterns tab, change the Border setting to None. ...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

How can I delete an expired contract? #2
I set the wrong expiration date on some contracts and now I want to delete them. The delete button doesn't work on contracts that expired. I tried to run a workflow that sets the contract status as Draft, but it didn't work either. Have you tried having the workflow change the status to canceled? Jarrett ...

how to hide an ActiveX control?
hi there i have an ActiveX control, written in C++, which has the OLEMISC_INVISIBLEATRUNTIME flag set, yet when i display the web page it is attached to, i get the familiar "red cross" graphic in the middle of the screen - how can i hide the control so nothing is displayed at all? tia bhu can u set the size of ur control as 1x1 pixels "bhu Boue vidya" <bhuvidya@yahoo.com.au> wrote in message news:1175703214.292931.252210@e65g2000hsc.googlegroups.com... > > hi there > > i have an ActiveX control, written in C++, which has the > OLEMISC_INVISIBLEATR...

View task pane and mail #2
Hello!! Using Outlook 2003 - Does anyone know if there is a way to view the mail as well as the task list? Similar to the view I can acheive using Calendar and task list. Thanks! Chris only by creating a folder home page using the outlook view control. There are some samples at www.digidashlive.com -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cente...

print 2 worksheets on the same page side-by side
I am creating a template that has two worksheets, I need to print the worksheets on the same page side-by-side. The problem I am having with them on the same page is when I hide the rows with zero values on one side it hides the the numbers on the other side You have a response at your other thread. wil4d wrote: > > I am creating a template that has two worksheets, I need to print the > worksheets on the same page side-by-side. The problem I am having with them > on the same page is when I hide the rows with zero values on one side it > hides the the numbers on the other s...

POS 2.0 running on HP rp5000
While idle HP POS printer advance paper 2cm and cut right edge of paper about 1 inch wide. Any assistance will be appreciated. ...