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.
ccravenbartleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.