HIdden Cells?

I would like to be able to hide columns automatically in a 
spreadsheet if no data was entered in the cells for that 
column without having to highlight the column and choosing 
hide. Eg: The spreadsheet has column headings, but for 
printing purposes I would like to hide that column if no 
data has been entered under any number of headings. Is 
there anyway this can be done automatically?
Thanks for your help

0
rose2 (1)
9/23/2003 7:19:20 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
389 Views

Similar Articles

[PageSpeed] 34

I wouldn't do it automatically when printing, but I would use a macro that I
could run on demand:

Option Explicit
Sub hideCols()

    Dim iCol As Long
    
    With ActiveSheet
        For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
            If Application.CountA( _
                    .Cells(2, iCol).Resize(.Rows.Count - 1)) = 0 Then
                .Columns(iCol).Hidden = True
            Else
                .Columns(iCol).Hidden = False
            End If
        Next iCol
    End With
    
End Sub
Sub showAllCols()
    ActiveSheet.Columns.Hidden = False
End Sub


Rosemary wrote:
> 
> I would like to be able to hide columns automatically in a
> spreadsheet if no data was entered in the cells for that
> column without having to highlight the column and choosing
> hide. Eg: The spreadsheet has column headings, but for
> printing purposes I would like to hide that column if no
> data has been entered under any number of headings. Is
> there anyway this can be done automatically?
> Thanks for your help

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/24/2003 12:04:25 AM
I wanted to do exactly the same thing, but with the unused rows
instead of the unused columns. Do I just switch Col with Row, or will
that not work?

Dave Peterson <ec35720@msn.com> wrote in message news:<3F70DF89.42F02F2A@msn.com>...
> I wouldn't do it automatically when printing, but I would use a macro that I
> could run on demand:
> 
> Option Explicit
> Sub hideCols()
> 
>     Dim iCol As Long
>     
>     With ActiveSheet
>         For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
>             If Application.CountA( _
>                     .Cells(2, iCol).Resize(.Rows.Count - 1)) = 0 Then
>                 .Columns(iCol).Hidden = True
>             Else
>                 .Columns(iCol).Hidden = False
>             End If
>         Next iCol
>     End With
>     
> End Sub
> Sub showAllCols()
>     ActiveSheet.Columns.Hidden = False
> End Sub
> 
> 
> Rosemary wrote:
> > 
> > I would like to be able to hide columns automatically in a
> > spreadsheet if no data was entered in the cells for that
> > column without having to highlight the column and choosing
> > hide. Eg: The spreadsheet has column headings, but for
> > printing purposes I would like to hide that column if no
> > data has been entered under any number of headings. Is
> > there anyway this can be done automatically?
> > Thanks for your help
0
9/24/2003 11:32:00 AM
It depends on how much you switched?

Did you remember to reverse the insides of the .cells(row,col) stuff?  And
xltoleft needs to be modified to xlup and a little bit more.  

Option Explicit
Sub hideRows()

    Dim iRow As Long
    
    With ActiveSheet
        For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Application.CountA( _
                    .Cells(iRow, 2).Resize(, .Columns.Count - 1)) = 0 Then
                .Rows(iRow).Hidden = True
            Else
                .Rows(iRow).Hidden = False
            End If
        Next iRow
    End With
    
End Sub
Sub showAllRows()
    ActiveSheet.Rows.Hidden = False
End Sub

This still looks at column A for headers and hides rows that have nothing in
them in B:IV.  Is that really what you wanted?




Eilidh wrote:
> 
> I wanted to do exactly the same thing, but with the unused rows
> instead of the unused columns. Do I just switch Col with Row, or will
> that not work?
> 
> Dave Peterson <ec35720@msn.com> wrote in message news:<3F70DF89.42F02F2A@msn.com>...
> > I wouldn't do it automatically when printing, but I would use a macro that I
> > could run on demand:
> >
> > Option Explicit
> > Sub hideCols()
> >
> >     Dim iCol As Long
> >
> >     With ActiveSheet
> >         For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
> >             If Application.CountA( _
> >                     .Cells(2, iCol).Resize(.Rows.Count - 1)) = 0 Then
> >                 .Columns(iCol).Hidden = True
> >             Else
> >                 .Columns(iCol).Hidden = False
> >             End If
> >         Next iCol
> >     End With
> >
> > End Sub
> > Sub showAllCols()
> >     ActiveSheet.Columns.Hidden = False
> > End Sub
> >
> >
> > Rosemary wrote:
> > >
> > > I would like to be able to hide columns automatically in a
> > > spreadsheet if no data was entered in the cells for that
> > > column without having to highlight the column and choosing
> > > hide. Eg: The spreadsheet has column headings, but for
> > > printing purposes I would like to hide that column if no
> > > data has been entered under any number of headings. Is
> > > there anyway this can be done automatically?
> > > Thanks for your help

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/24/2003 10:47:03 PM
Reply:

Similar Artilces:

Cells not adjusting to new Column
Currently, I have the following formula in Column J of the audit sheet: =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) If I insert a column in the audit sheet, the formula in Column J is now in Column K, but the formula stays the same. =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) I would want the formula to adjust to: =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) All three sheets (audit, upload, and control) are in the same workbook. Is there a way to make the formulas adjust automatically when a column is deleted or ...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

How can I protect only the highlighted cells in Excel?
How can I protect only the highlighted cells in Excel? 1. select whole worksheet 2. right-click mouse and select the "format cells" option 3. click the "Protection" tab 4. untick the "locked" and press OK 5. highlight the cell the want to protect 6. right-click mouse and select the "format cells" option again 7. click the "Protection" tab 8. tick the "locked" and press OK 9. go to tools -> protection - > protect sheet 10. assign password 11. OK "John White" <John White@discussions.microsoft.com> ���g��l��s�D...

How do you select a cell with an "absolute" address with a cursor?
When you try to make an absolute reference to a cell, you place "$" in front of the column and/or row. But can you do it more easily by, for example, selecting a cell with your mouse while pressing CTRL (obviously this does not work) when you select a cell? Thank you. New Daddy, select the cell press F2 then F4 to toggle absolute -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "New Daddy" <newlywed2@daum.net>...

Using arrow keys to move between cells
Hey guys, Sorry for dumb question, but how do I move between cells w/o using my mouse and rather using arrow keys? Do you have use any of shift, alt, ctrl keys at same time? Thanks. If pressing the arrow keys are not moving you between cells, check and see if scroll lock is on on the key board, here is some info from help Arrow keys Move one cell up, down, left, or right. CTRL+arrow key Move to the edge of the current data region. HOME Move to the beginning of the row. CTRL+HOME Move to the beginning of the worksheet. CTRL+END Move to the last cell on the worksheet, in the bottom-most us...

Up/down arrows don't work with data validation list/in-cell dropdown
I have a number of worksheets where I use the in-cell dropdown (from list) function of data validation. When I open the list by either clicking on the drop down button or by pressing Alt-Down arrow, the only way I can move through the list is to grab the scroll bar itself and move it down. The up and down buttons do not function nor does clicking in the open space just above or below the scroll bar. Any ideas as to what is causing this? David Jones David, I just set up an entire column with Data Validation with List. I used 39 entries in the list. The up/down buttons are working as they ...

How do I make a picture stay inside a cell?
I'm trying to generate a spreadsheet to track artwork. I'd like to have a thumbnail of the art in the spreadsheet alongside the price and size info I'll be entering. When I insert a picture it just 'floats' on top of the spreadsheet seemingly unattached to any cell. I got excited that there was a solution when, in picture formatting, I saw an option to size and move with cell, but that did nothing. Is there a way to insert a picture into a cell? Cells can contain only formulas or values. Pictures always "float" in the Drawing Layer "above" th...

Copy Cells
Hi all I have a spreadsheet which has lots of calls logged on it. Each call is logged by a person. I have a tab for each person. What i need to do is each persons tab needs to pick up the information for that person (pick it out of the main sheet which has everyones call on it). So any cell in column 'A' with the name 'Joes Bloggs' needs to copy accross to tab 'Joe Bloggs' along with the rest of the information on that line. Hope this makes sense, if you dont understand reply with a question. Thanks in advance Darren --- Message posted from http://www.ExcelForum...

Displaying the zero value in a cell
Is it possible to display a zero at the front of a series of numbers in a cell? Eg. "0123" is displayed as "123". How to display as original "0123"? I've got the 'zero value' checked in Tools - Options - View. That is only for a zero value in a cell without any other numbers in that cell. Thanks in advance!! Sure. Just type it with an apostrophe in front, or format the cells. For a good example, choose Format-Cells, Number tab. Then choose Special-->Zipcode. It shows "00000". So if your numbers are 10-digit account numbers, put "...

Linked cells in Multiple Workbooks
I am tracking dates and have linked cells from another workbook so as t not have to duplicate the data entry of dates. The dates are linked t projects that are listed in rows and there is information in m workbook that is additional information within the row. So if someon changes the order of the other workbook, the information that is in m workbook does not get sorted with the order. Is there a way to link m cells to the linked cells? ie. Project ID Start Date Milestone EndDate Person Assigned 1234 (linked) 11/15/04 11/20/04(lin...

Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and deposits. Current balance is determined by subtracting withdrawals and adding deposits to the balance in the line above. Suddenly I have started getting a cell entry #VALUE! in the current balance cell. A typical entry in the cell is for G43 would be: =G42-E43+F43 and the sudden new result is #VALUE. All three columns have cells formatted as numbers. HELP! On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote: > I have a spreadsheet with three columns: Current balance, withdrawals, and > deposit...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Cells and Charts not updating!
I have created a large spreadsheet (80Megs), The charts and other cells just simply do not update when I make changes. Is this due to the size of the spreadsheet? Thanks, GH G Hood wrote: > I have created a large spreadsheet (80Megs), The charts > and other cells just simply do not update when I make > changes. Is this due to the size of the spreadsheet? Did you check the automatic recalculation? Tools | Options | Calculation tab | Automatic or Manual Calculation. Dave dvt at psu dot edu Yes, I have already checked, it is set to automatic calculation. >-----Original Mes...

Block entry into a cell if another cell has a value
I am working on a W-4 Federal Tax Form created in Excel. I want to be able to block an entry in one cell (Total number of allowances) if another cell (Write *Exempt* here) has a value, specifically Exempt, in it and vise versa if the cell (Total number of allowances) has a value, specifically a number, in it I want block any entry into the cell (Write *Exempt* here) I hope this makes sense to somebody out there. Old Navy, You can use Data - Validation. I'll use A1 and B1. Select A1. Data Validation - Custom: =B1<>"" If you want to appear a little more refined, you ca...

How to refresh all Cells in an Excel Workbook automatically?
I have an Excel Workbook that is Shared and it uses the Vlookup function to look up values in another Excel Workbook. Both files are stored in a Network drive and I use \\server_name\shared_folder\filename.xls format to reference the file in the first Workbook. Problem: Upon opening the Shared Workbook, it does not always display the correct Cells values until I manually press F2 and Enter on each of the wrong-value Cell. Is there a way to refresh all the Cells' value without manually pressing F2 and Enter on the affected Cells? Maybe Edit|Links|Update values Or even selecting all...

Paste Cell Contents into Custom Header
I'm setting up a Custom Header under File...Page Setup and I want to be able to include the contents of a cell in the Header. While the cell address stays the same for every worksheet, the value within the cell will change. Is this possible? Thanks, Barry Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.CenterFooter = .Range("b12").Text End With End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "RoadRunner News" <nospam@nowhere.com> wrote in message news:46549906$0$16730$4c368faf@roadrun...

Modifying cell entries?
Hi all, I have a range of cells in a spreadsheet containing values, say cell A1 : C5. Example as follows: A B C 1 100 100 100 2 100 100 100 3 100 100 100 4 100 100 100 5 100 100 100 I often need to edit all the individual cells in the range to become "=100*86%" because i need to keep the original value in the cell as well as shown the resulted value in the cells too, in this case it is 86 (result after 100 multiple by 86 percent). How do i cr...

Cell referencing on a chart
I would like to be able to reference a cell in a textbox on a chart. Ex. Cell A1 value = "12" Text box on chart = "12" I need to be able to change cell A1 and have the textbox change with it. Please give me advice on how to do this. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ 1) Click on chart to activate it 2)In formula bar, type = 3) Use mouse to point to a cell (formula now reads =Sheet1!A1 4) Click green check mark in Formula Ba...

Display cell formula by function
Hi. Is there a way to perform a function which display the formula of the target cell (NOT the value of the cell)? {Fictitious function} ShowFormula(TargetCell) Eg: A1 = sum(3, 4) A2 = ShowFormula(A1) Answer of A2: sum(3, 4) -- Additional information: - I'm using Office XP - I'm using Windows XP You could copy the equation to the cell and then put " in front of it (short term) "0-0 Wai Wai ^-^" <x@x.com> wrote in message news:%23OeHX84sFHA.2880@TK2MSFTNGP12.phx.gbl... > Hi. > Is there a way to perform a function which display the formula of the >...

Using the same cell
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel If i have values in a column and want to copy those values over and over again how do I do it? Say that cell e6 has a value of cell e2 (it actually has "e2" in the cell) and the value in cell e2 is $100. I wish to copy the value of e6 to columns f thru z, but want the value copied to be e6 (otherwise they would change to f6, g6, etc). That way if I change the value of e2, every cell with e6 in it will change too. It seems to me that I used to enclose the value in quotes or parenthesis and that would copy over ...

Cell widths vary on different computers
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a problem that when a worksheet is created on one computer then opened on another the cell widths are different, with the result that pages look inconsistent if we need to print out or create a pdf for a client to view. <br><br>We have gone through and ensured that all computers are running the same system, same updates installed, same fonts installed. <br><br>Can anyone help? In article <59bb0f93.-1@webcrossing.JaKIaxP2ac0>, Spectre@officeformac.com wrote: > Version: 20...

Excel 2000: Lines hidden with filter
With a tabelle of daten. After I have filtered my lines with a criterium, I decide to hide those lines. But when I go back on to my fiter to release the criterium, I would like the filter to remember that I have hidden the previous lines, so that they don t appear again. How can I do this? Thank you. Simon do you mean that you want to filter the lines again? "Simon" <Simon@discussions.microsoft.com> wrote in message news:83DE9BD8-E925-41FC-BE25-E2406EC3B19A@microsoft.com... > With a tabelle of daten. > After I have filtered my lines with a criterium, I > de...

How to generate a cell reference from multiple cells
For example, to acquire the cell reference 'c5' the C is generated by a formula in one cell, and the 5 is generated in a different cell Try this... A1 = C B1 = 5 =INDIRECT(A1&B1) Which evaluates to =$C$5 -- Biff Microsoft Excel MVP "Malters" <Malters@discussions.microsoft.com> wrote in message news:F8BDB88A-7371-4C02-8973-B7E8F3E5E50D@microsoft.com... > For example, to acquire the cell reference 'c5' the C is generated by a > formula in one cell, and the 5 is generated in a different cell ...

Replace cell of column with cells of other worksheet
Hello, I have the following problem. In one sheet I have a table with imported keys from an access table. Now I want to replace these keys with strings that are in a second worksheet. So eg when a cell in the 1st sheet has value 2 it has to be replaced by eg "the second"... How can I do this for all the cells of that column automatically? Thanks a lot. Kristof I'd suggest VLOOKUP, if it weren't for > all the cells of that column > automatically Find & Replace is probably out, since what works for "2" will affect 12, 20, 21, 22 ... Depending ...

Is it possible to search for a particular format in cells?
I am in the process of compiling together lots of analytical data concering metals concentrations in sediment samples that we send out to various labs for analysis. For instance, sometimes lab A lists the percent error of silver as 5.6% by using percent formatting on a cell with the value .056. Lab B will list the error as 5.6 with general formatting (which I prefer). The problem with the first situation is if the formatting of the cell is ever lost (ie: 5.6% becomes .056). I eventually compile all of the results from the various labs onto one giant spreadsheet and in the proces...