• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2571
  • Last Modified:

Excel VB.Net : How to programatically hide unused columns and rows from worksheet

I am developing a VB.Net application that generates Excel spreadsheets.  In each spreadsheet, I want  to hide all unused columns and rows so that the user is presented with the minimum area to work with.

For example, one spreadsheet template has columns A-J and rows 1-100.  I want to hide all columns from K to whatever the end column is (which is Excel version dependent) and I want to hide all rows from 101 to the end row (which is also Excel version dependent).

What is the best way of doing this.
0
ccravenbartle
Asked:
ccravenbartle
  • 5
  • 4
1 Solution
 
TracyVBA DeveloperCommented:
This is how you do it in VBA, for any version (doesn't matter the size of the grid):
Sub HideUnusedRowsAndCols()

    Dim lastRow As Long
    Dim lastCol As Integer
    
    Application.ScreenUpdating = False
    
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    lastCol = Range(Columns.Count & ":1").End(xlToRight).Column
    
    Columns(GetColumnLetter(lastCol + 1) & ":" & GetColumnLetter(Columns.Count)).EntireColumn.Hidden = True
    Rows(lastRow + 1 & ":" & Rows.Count).EntireRow.Hidden = True
    
    Application.ScreenUpdating = True
    
End Sub

Function GetColumnLetter(ColumnNumber As Integer) As String
'Converts Column Number to Equivalent Letter
    If ColumnNumber < 27 Then
        ' Columns A-Z
        GetColumnLetter = Chr(ColumnNumber + 64)
    Else
        GetColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                          Chr(((ColumnNumber - 1) Mod 26) + 65)
    End If
End Function

Open in new window

Hide-Unused-Space.xls
0
 
BullmanTechCommented:
If you're generating the Excel workbooks with your code, do you need to be concerned with the idea of Excel version-dependencies? You just need to make the file creation and editing work for your individual setup, right? Or is there some other consideration necessary? Please let us know what exact considerations you're concerned with and why. Thanks.

Sean
0
 
ccravenbartleAuthor Commented:
broomee9 : the hide rows code works but the hide columns GetColumnLetter function fails with large column counts e.g. 16384
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
TracyVBA DeveloperCommented:
Change from a Long to an Integer (although an Integer can go up to 32,767).  Try the attached.
Hide-Unused-Space-v2.xls
0
 
TracyVBA DeveloperCommented:
Oops, I meant to say change from an Integer to a Long.
0
 
ccravenbartleAuthor Commented:
Sean:  

The application lets the user select the Excel version type, e.g. Excel 97-2003, Excel 2007, to generate different types of spreadsheet for different users.  

However, in the Excel object I now see that there is a rows.count and a columns.count property which seems to have the maximum values for rows and columns so I guess I could use that somehow.  I just can't work out how to set the column range to apply the .EntireColumn.Hidden method.
0
 
ccravenbartleAuthor Commented:
broomee9 : The problem is that the Chr() function in GetColumnLetter only accepts values from 0 to 255.
0
 
TracyVBA DeveloperCommented:
OK, I modified it to get rid of the function.

You can simply use this to hide the columns:

    Range(Cells(1, lastCol), Cells(1, Columns.Count)).EntireColumn.Hidden = True
Sub HideUnusedRowsAndCols()

    Dim lastRow As Long
    Dim lastCol As Long
    
    Application.ScreenUpdating = False
    
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    lastCol = Range(Columns.Count & ":1").End(xlToRight).Column
    
    Range(Cells(1, lastCol), Cells(1, Columns.Count)).EntireColumn.Hidden = True
    Rows(lastRow + 1 & ":" & Rows.Count).EntireRow.Hidden = True
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

Hide-Unused-Space-v3.xls
0
 
TracyVBA DeveloperCommented:
The code isn't in v3 for some reason, so I added it in here for v4.
Hide-Unused-Space-v4.xls
0
 
ccravenbartleAuthor Commented:
Thank you for your help.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now