Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding blank rows and columns (ExcelXP)

    Is there is an easy way to hide unused rows and columns in Excel? At the moment, I select the rows all the way down to the end of the spreadsheet and then choose the Format Menu, and "Row/Hide". I do the same for the columns. However, it is very time consuming.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    These two VBA macros will do that:

    <pre>Option Explicit

    Public Sub HideEmptyRows()
    Dim I As Long, J As Long
    With ActiveSheet
    For I = .UsedRange.Row + .UsedRange.Rows.Count - 1 To 0 Step -1
    If Application.WorksheetFunction.CountA(Range("A1").O ffset(I, 0).EntireRow) <> 0 Then
    Exit For
    End If
    Next I
    For J = I To 0 Step -1
    If Application.WorksheetFunction.CountA(Range("A1").O ffset(J, 0).EntireRow) = 0 Then
    .Range("A1").Offset(J, 0).EntireRow.Hidden = True
    End If
    Next J
    End With
    End Sub

    Public Sub HideEmptyCols()
    Dim I As Long, J As Long
    With ActiveSheet
    For I = .UsedRange.Column + .UsedRange.Columns.Count - 1 To 0 Step -1
    If Application.WorksheetFunction.CountA(Range("A1").O ffset(0, I).EntireColumn) <> 0 Then
    Exit For
    End If
    Next I
    For J = I To 0 Step -1
    If Application.WorksheetFunction.CountA(Range("A1").O ffset(0, J).EntireColumn) = 0 Then
    .Range("A1").Offset(0, J).EntireColumn.Hidden = True
    End If
    Next J
    End With
    End Sub
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    Legare, I have put this code behind the worksheet and tried to run it. However, it doesn't do anything.

    Doesn't Excel have a built in way of hiding unused rows and columns?

    Many thanks.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    Try putting them in a standard module and calling them with a command button.

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    The code hides blank rows and columns within the useable area. Is there an easy way to hide all the blank rows and columns outside the useable area?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    Sure can:

    <pre>Option Explicit

    Public Sub HideEmptyRows()
    Dim I As Long, lLastRow As Long
    With ActiveSheet
    lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1
    For I = lLastRow To 0 Step -1
    If Application.WorksheetFunction.CountA(Range("A1").O ffset(I, 0).EntireRow) = 0 Then
    .Range("A1").Offset(I, 0).EntireRow.Hidden = True
    End If
    Next I
    If lLastRow < 65535 Then
    Range(.Range("A1").Offset(lLastRow + 1, 0), Range("A65536")).EntireRow.Hidden = True
    End If
    End With
    End Sub

    Public Sub HideEmptyCols()
    Dim J As Long, lLastCol As Long
    With ActiveSheet
    lLastCol = .UsedRange.Column + .UsedRange.Columns.Count - 1
    For J = lLastCol To 0 Step -1
    If Application.WorksheetFunction.CountA(Range("A1").O ffset(0, J).EntireColumn) = 0 Then
    .Range("A1").Offset(0, J).EntireColumn.Hidden = True
    End If
    Next J
    If lLastCol < 255 Then
    Range(.Range("A1").Offset(0, lLastCol + 1), Range("IV1")).EntireColumn.Hidden = True
    End If
    End With
    End Sub
    </pre>

    Legare Coleman

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    Along with the VBA answers you've recevied, you can make the rows/cols 'appear' hidden. Since I'm not sure what your goal is - a visual thing to not show empty cells?

    For sheets that I know the user doesn't need to add their own formulas I'll often turn off the gridlines and the row/col headers (see the bottom part of the menu Tools/Options). It makes the sheet less cluttered but it does depend on what the sheet is used for whether or not it's a good thing to do.

    You can also define the scroll area of the worksheet so the user can't scroll too far right or down (they can't see what they can't get to). The scroll area is set in the sheet properties from the VBA tool bar.

    Deb

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding blank rows and columns (ExcelXP)

    Have you possibly formatted your cells by selecting the entire column and formatting with a comma, for example. Or highlighted the entire first Row and formatted as bold, for example? Is it possible that this would make the cells show as "used", and Legare's macro may not work because of that?

Posting Permissions

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