Hide Column if Grand Total is 0

I have a macro that subtotals based on sales order. If a column (e.
price adjustment) has a Grand Total value of 0 I hide the column befor
printing the report.

Is there a macro that could search for the word Grand Total then 
go to each column starting from H through T on the Grand Total Row, se
if the value is 0 and hide the column if the value is 0.

Any help will be greatly appreciated. 

S

--
Message posted from http://www.ExcelForum.com

0
4/23/2004 5:03:00 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
477 Views

Similar Articles

[PageSpeed] 58

Hi,
Try something like:

Sub HideGT()
For Each c In Range(Cells(1, "A"), Cells(ActiveCell.End
(xlDown).Row, "A"))
If UCase(c.Value) = "GRAND TOTAL" Then
    For Each d In Range(Cells(c.Row, "H"), Cells
(c.Row, "T"))
        If d.Value = 0 Then
            Columns(d.Column).Select
            Selection.EntireColumn.Hidden = True
        End If
    Next d
    Exit Sub
End If
Next c
End Sub

jeff
>-----Original Message-----
>I have a macro that subtotals based on sales order. If a 
column (e.g
>price adjustment) has a Grand Total value of 0 I hide 
the column before
>printing the report.
>
>Is there a macro that could search for the word Grand 
Total then 
>go to each column starting from H through T on the Grand 
Total Row, see
>if the value is 0 and hide the column if the value is 0.
>
>Any help will be greatly appreciated. 
>
>SK
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
4/23/2004 6:56:44 PM
Thanks Jeff. I had to add to the macro to rename Grand Total to a numbe
run your code adn then rename it bak to Grand Total to get it to work.

I really appreciate your help

--
Message posted from http://www.ExcelForum.com

0
4/23/2004 8:36:20 PM
Reply:

Similar Artilces:

Crystal Reports-totalling a Summary
Have a question on totalling a summary field in Crystal Reports, I am using the Minimum, Maximum and average functions to summarize purchase order line items for a bill of materials. I would also like to total these min, max and averages for all the items on a bill of materials but I cant figure out how to make crystal do this. maybe it isnt possible? I get the message that I cant total on a running total field or summary field. any suggestions? -- Doug Doug you may want to go on over to http://www.tek-tips.com/threadminder.cfm?pid=149 I go to this forum and others on tek-tips....

VC++ 6.0 projects
Hi, I have some VC++6 projects that I need to do some work on but don't have the VC++6 compiler. The projects are a few years old (they target Windows 98). Can I use the latest VC++ (.net) to open + change + recompile my VC++ 6 projects? The modified apps will be deployed on windows 98 machines. I would preferably like to use VC++6 but I can't find anyone that sells it anymore, so maybe its been discontinued? At the moment I am still using windows 98 on my development machine too. According to Microsoft product information, the latest VC++ doesn't say that it will run ...

calculated total in query
I have 2 fields in query. Qty1 and Qty 2. I added Total:qty1+qty2. However, if qty1 has some number but qty2 is blank, my total is blank, instead of just show the sum. In Access You need to use the NZ function to force a ZERO. Or use an IIF clause. Total: Nz(Qty1,0) + Nz(Qty2,0) or Total: IIF(Qty1 is Null,0,Qty1) + IIF(Qty2 is Null,0,Qty2) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Song wrote: > I have 2 fields in query. Qty1 and Qty 2. I added Total:qty1+qty2. > However, if qty1 has some number b...

Format Column
I have a column with inmate id numbers in it. I imported them from a text based program. I made a custom formatting for the column because all the id numbers begin with zero. IE 00112356 or 01555666 etc... The format I used was 00000000 under custom. The problem now is that I want to import them into access but access doesn't see the zero at the beginning. How can I make it show the literal number including the zeros? I tried adding the '01222555 before the number, but I would have to manually append 2000 records. Is there a faster way? Morph. Hi you could use a helper column...

sum by coding without 0 #2
hi in the following program if there is no value in the cells a1 and b1 it returns 0 Public Sub Add2Cells() Range("C1").Value = Range("A1").Value + Range("B1").Value End Sub i need only the output if the both the cells have the data will you can tell me that Public Sub Add2Cells() If Range("A1").Value <> "" And .Range("B1").Value <> "" Then Range("C1").Value = Range("A1").Value + Range("B1").Value End If End Sub -- HTH Bob (there's no email, no ...

move sharepoint databases Business Portal 4.0
I am attempting to move wss 3.0 databases from a sql 2000 to a sql 2005 on same server. I read the article id 862672, but this seems to apply to wss 2.0. I read a technet article that says to use preparetomove stsadm command before detach, but I get an error message that says ""Missing operation name or the operation name is invalid". Is there a new procedure for move sharepoint databases available? ...

Hide formatting marks
Would anyone be able to advise how I get the formatting marks to be hidden in my return e-mails that I reply to? Please advise Thanks! when you reply to your mail, goto tools-->options-->view and under formatting marks, deselect whichever you dont want to see. regards, Subbu. --- Subramanian .S v-subs@online.microsoft.com Microsoft GPS This posting is provided "AS IS" with no warranties, and confers no rights. ...

Different Total for end of day sales report
I am currently using RMS 1.2 on a Windows XP/SP2 computer. My problem is when i run various reports at the end of the day like Tender Summary and Detailed Sales report to see the total sales for the day, neither one matches my physical count. Can someone explain to me why i am getting this kind of result? And what i can do to correct it or get the right information? Or maybe i need a custom report to get an accurate total? Thank you in advance. Tender summary and detailed sales reports will have different totals since one (tender summary) includes all monies taken in, including taxes,...

Time Sheet
I have a time sheet with four fields: Time In (C2), Lunch Out (D2), Lunch In (E2), Time Out (F2) I'm trying to create a formula in cell G2 to calculate the regular hours and another formula in H2 for the overtime hours, but have been unsuccessful. Current fields are formatted as Number | Custom | h:mm AM/PM for C2 thru F2 and are formatted as Number | Custom | h:mm for G2 and H2. Can anyone help me. Hi, Look into CPearson Web it has all the working with overtime Hs http://www.cpearson.com/excel/overtime.htm "Kathy" wrote: > I have a time sheet wi...

Data to transfer from column to row #2
Dear All, I have a cell that contains like: PM-1234, PM-2345, PM-5689, PM-9999. I want to segregate these 4 tag into rows. i.e., PM-1234 PM-2345 PM-5689 PM-9999 Is there any excel guru out there to help in my problem??? Your help is greatly appreciated. Thank you, qwerty7 -- qwerty7 ----------------------------------------------------------------------- qwerty70's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3267 View this thread: http://www.excelforum.com/showthread.php?threadid=52474 If you highlight the four cells containing the data, then click <...

How to display the TOTAL of a sub form on the parent Form?
on my sub form (subDetails) I have a totals column. I then Total the totals column in the footer of the subform in a field named (txtOrderTotal) I want to display that total on the Parent form (frmOrders) (I do not want to just show the footer of the sub form due to placment issues) So on my parrent form I have a text box with this code in the control source =Forms!subDetails.txtOrderTotal BUT all I get is "#NAME?!" I have doubled checked my spelling. Am I going about this wrong? Any help here will be appreciated. Thanks in advance Dave On Fri, 24 Aug 2007 10:38:35 -0700...

How do I split a column of information into two?
I have a column of information that I would like to split into two columns. As follows Mr John Smith (johnsmith@hotmail.com) Mrs Katie Brown (katiebrown@hotmail.com) I need to put everything in the bracket into a new column! Can anyone help? One way, assuming data as posted in A2 down In B2: =LEFT(A2,SEARCH("(",A2)-1) In C2: =RIGHT(A2,LEN(A2)-SEARCH("(",A2)+1) Select B2:C2, copy down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Nigel" <Nigel@discussions.microsoft.com> wrote in message news:64C2A4BD-CCF9-439...

Re-linking a completed email (CRM 3.0)
Hi there Is it possible to re-open a 'completed' email activity and change the 'Regarding' link? our users are (not infrequently) linking to the incorrect opportunity when promoting an email from Outlook 2003. Cheers Peter The same story with my users. I will be very insterested in finding a solution. "Peter Haigh" wrote: > Hi there > > Is it possible to re-open a 'completed' email activity and change the > 'Regarding' link? our users are (not infrequently) linking to the incorrect > opportunity when promoting an email from Ou...

Link row to Column
I have a spreadsheet that has multiple tabs. I want to have a row of data be linked to a column on the other tab. For example: A1 linked to A1 B1 linked to A2 C1 linked to A3 D1 linked to A4 Is there an easy way to transpose the link so that I don't have to manually create the link for each one? I have about 70 or so and growing. Try: Cell A1: =INDIRECT("Sheet2!A" & COLUMN(A1)) and drag across the columns Regards Trevor "Aaronous" <Aaronous@discussions.microsoft.com> wrote in message news:8EA38615-6B24-402B-8C2A-78E4189A4C45@microsoft.co...

Copy the last cell in an Excel column to another cell
I am a novice excel user. I have a spread sheet that continually changes, rows and columns add or subtract up to a total. The total column contiunues to grow in length as new entries are made. Can this last cell with an entry be copied to another cell, for example in a header back at the top of the spread sheet? I have tried to find a function to no avail...MAX wont work becasue the number can get smaller. Thanks in advance. Assuming that Column C contains the 'Total', try... =LOOKUP(9.99999999999999E+307,C:C) Hope this helps! In article <79AF897E-5359-4786-9E60-6A2884...

Converting from 6.0 to VS2005
I have a clean compile but when I try to link I get bunch of unresolved external symbol _DebugTrapFn Any ideas what I'm missing please ? A .lib file. Who defines that function? A strong hint is the name of the module that it is unresolved in, but you didn't bother to tell us that. joe On Mon, 5 Feb 2007 16:33:05 -0800, "Michael Tissington" <mtissington@newsgroup.nospam> wrote: >I have a clean compile but when I try to link I get bunch of unresolved >external symbol _DebugTrapFn > >Any ideas what I'm missing please ? > Joseph M. Newc...

Align receipt column to the left
Hi All, I have the following XML code from my a4 receipt. All I am trying to do is make the row <ROW> "||Discount||" Entry.ReasonDiscount.Code "|" align left so that it appears under my lookup code or description (i'm using entry.comment) I cannot seem to get this to work and I am very confused by the alignment command. Can anyone please help? It's an full page receipt using Opos drivers. I have included the code concerned below; <TABLE> <TOP> HeaderHeight + MarginTop </TOP> <FONT> "Small"...

Error message when hiding columns
Has anyone seen this error message before when trying to hide columns? "Cannot shift objects off sheet." -- valenj ------------------------------------------------------------------------ valenj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24087 View this thread: http://www.excelforum.com/showthread.php?threadid=376912 maybe this'll help: XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 valenj wrote: > > Has anyone seen this error message before whe...

Column-Line Chart w/ Dates
I have a chart that is using columns for category "A" on the primary x-axis (a $ amount). This category is broken down first by person, then by month. So essentially from left to right the columns will be person "1" and month "1", person "1" and month "2", person "1" and month "3", person "2" and month "1", and so on.... The chart is also using lines to mark category "B" on the secondary x-axis (a $ amount that is a portion of category "A", but not a percentage. Essentially c...

Payable not match to total vendor ballance
What is possible reason account payable does not match to total of vendors ballances? 1. a gl batch was deleted before it was posted. 2. someone posted directly to the gl account and not through payables. several reasons. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "icc" wrote: > What is possible reason account payable does not match to total of vendors > ballances? As Richard stated, there are a num...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "msdobe" <msdobe@discussions.microsoft.com> wrote in message news:529B3703-9D9A-4560-80D...

OLE Notes
Does anyone know why I am not seeing the OLE notes button in the customer card note attachment window? Thanks! Craig Craig, Make sure that the OLE objects Path is defined in your DEX.ini. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "Craig" wrote: > Does anyone know why I am not seeing the OLE notes button in the customer > card note attachment window? > > Thanks! > > Craig Yes, the path has been specifie...

Hiding Columns #2
I have a spreadsheet where 5 identical sheets feed into a summary sheet. The columns are dates where people enter the time that they have spent on a certain job. In the summary sheet I have a formula adding the corresponding cell in each of the sheets. I was wondering if anybody knew how I could hide a column on the summary sheet only if the formula result is zero from the data coming from the 5 other sheets. Thanks, Jane And if you really meant columns, you could use a macro: Option Explicit Sub testme01() Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets(...

Excel hiding everything except the work on the sheet?
Hi, I am wondering if there is a quick way to hide all the columns outside the rows and columns not used in my spreadsheet? Currently I do this by selecting and dragging but the rows take a while to get to the end. Is there an easier way to do this? I am using excel 2003 TIA Patrick CRTL + Right arrow will slect to comumn IV Format>Columns>Hide will hide these. CRTL + Downarrow for rows. Gord Dibben Excel MVP On Mon, 14 Jun 2004 19:04:00 -0700, "Patrick" <patrickm@spamsonic.net> wrote: >Hi, > I am wondering if there is a quick way to hide all the columns ou...

Jet 2.0 dB corruption under Vista
Old VB 3.0 app using Jet 2.0 engine runs fine under very OS from 3.1 to XP but hangs then corrupts DB under Vista Home Premium. All running locally - not network shared, so that KB article doesn't apply (seems to be for 4.0 anyway). Any thoughts or is this the final nail in the coffin for Vista? It came pre-installed on the laptop, and I've had just about enough of the problems caused by this OS and might upgrade to XP... On Wed, 16 Jan 2008 20:31:31 -0700, "V Green" <vanceg@nowhere.net> wrote: >Old VB 3.0 app using Jet 2.0 engine runs fine >under very OS f...