Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hiding Rows or Columns (Excel 2003)

    I'm looking for a formula that hides a row or column if there is no data there ?
    Any ideas ?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: hiding Rows or Columns (Excel 2003)

    Could you be more specific about. You can't do what you are describing with a formula, though you could do it with a macro and we would need more details on the goals to give suggestions.

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hiding Rows or Columns (Excel 2003)

    Hi,

    It's unlikely to be solved by a formula. You'd need a macro to test a column for data (by the way, do you mean only numbers, only text, or any combination of both?) and then hide that column.

    For example, to hide col A if it has NOTHINg (text or data) in it:
    (Cell B1 should be empty so as not to overwrite important data!)

    Sub HideColIfEmpty
    Range("B1").Formula = "=counta(A:A)"
    If Range("b1") = 0 Then
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    End If
    End Sub

    HTH

  4. #4
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hiding Rows or Columns (Excel 2003)

    Row 1 = column headings
    Row 13 = Totals

    There is data in rows 2 to 4 - what I want to do is hide rows 5 to 12 where there is no data

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: hiding Rows or Columns (Excel 2003)

    Try using these macros

    This will look thru the "used range" of the spreadsheet and hide all the blank rows and columns within it

    <pre>Option Explicit
    Sub HideRowsCols()
    Dim rng As Range
    Dim x As Long
    Dim WF As WorksheetFunction

    Set WF = Application.WorksheetFunction
    Set rng = ActiveSheet.UsedRange

    For x = 1 To rng.Columns.Count
    If WF.CountA(rng.Columns(x)) = 0 Then _
    rng.Columns(x).EntireColumn.Hidden = True
    Next
    For x = 1 To rng.Rows.Count
    If WF.CountA(rng.Rows(x)) = 0 Then _
    rng.Rows(x).EntireRow.Hidden = True
    Next
    Set WF = Nothing
    Set rng = Nothing
    End Sub</pre>


    This macro can be used to unhide all the rows and columns of the worksheet

    <pre>Sub UnhideRowsCols()
    With Cells
    .EntireColumn.Hidden = False
    .EntireRow.Hidden = False
    End With
    End Sub</pre>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •