Hide excel columns from column K to the right using vb.net

[gbhs]

I have created a workbook using VB.net and I need to Hide all columns to the right of Column K in all worksheets.

To hide column K alone I can do

Worksheet.Range("K:K", missing).EntireColumn.Hidden = true

How can I hide column K and ALL the columns to the right of column K?

The Following macro can do it but how can I include it in my VB.Net button click event

Sub Macro2()
    Columns("K:K").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireColumn.Hidden = True
End Sub

Help me out!

[gbhs]

It does works with

    Dim y As Integer = 12    'hide all cols from 12th col
    While y <= 16384         'happen to know that a sheet has 16384 cols
        oSheet.Columns(y).ColumnWidth = 0
        y = y + 1
    End While

Not an optimal solution.
Any ideas?

[cgeier]

The following has been tested in Excel 2010:

Add a reference to Microsoft Excel xx.x Object Library

• Project
• Add Reference
• COM
• Microsoft Excel 14.0 Object Library

Create a new module named "Module1.vb".

Module1.vb:

Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

    Public Enum ColumnVisibility
        Hide
        Show
    End Enum


    Public Sub setColumnVisibility(ByVal filename As String, ByVal visibility As ColumnVisibility)

        Dim xlApp As Excel.Application = New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlPreviousActiveSheet As Excel.Worksheet
        Dim xlSheet As Excel.Worksheet
        Dim xlRng As Excel.Range

        'start Excel and get Application object
        xlApp = CreateObject("Excel.Application")

        'change to False if you don't
        'want Excel to be visible
        xlApp.Visible = True

        'open workbook
        xlWb = xlApp.Workbooks.Open(filename)

        'get previously active sheet
        'so we can make it the active sheet
        'again before we close the file
        xlPreviousActiveSheet = xlWb.ActiveSheet

        For i As Integer = 1 To xlWb.Sheets.Count

            xlSheet = xlApp.Sheets(i)

            'activate current sheet
            'needed for "Select"
            xlSheet.Activate()

            'get range of sheet
            xlRng = xlSheet.Cells()

            'Console.WriteLine("Total Rows: " & xlRng.Rows.Count)
            'Console.WriteLine("Total Columns: " & xlRng.Columns.Count)

            'select range
            xlSheet.Range(xlSheet.Cells(1, 10), xlSheet.Cells(xlRng.Rows.Count, xlRng.Columns.Count)).Select()

            If visibility = ColumnVisibility.Show Then
                'show columns in range
                xlApp.Selection.EntireColumn.Hidden = False
            Else
                'hide columns in range
                xlApp.Selection.EntireColumn.Hidden = True
            End If

            'undo selection
            xlApp.CutCopyMode = Excel.XlCutCopyMode.xlCopy
            xlSheet.Range("A1").Select()
        Next

        'make previous active sheet
        'the active sheet again
        'before we close the file
        xlPreviousActiveSheet.Activate()

        'close and save changes
        xlWb.Close(SaveChanges:=True)

        'quit Excel
        xlApp.Quit()

    End Sub

End Module  

Usage:

setColumnVisibility("C:\Temp\Book1.xlsx", ColumnVisibility.Hide)

Or

setColumnVisibility("C:\Temp\Book1.xlsx", ColumnVisibility.Show)

Resources:

VB.NET Excel

How to select cells/ranges by using Visual Basic procedures in Excel

How do I set selection to Nothing when programming Excel using VBA

Workbook.Close Method

[gbhs]

Thanks
I finally got it this way

'HIDE UNUSED ROWS AND COLS
   With oSheet
   Dim lastRow As Long
   Dim lastCol As Long

   Dim Rows As Integer = ds.Tables("tblStudents").Rows.Count + 1
   Dim Columns As Integer = 12

   lastRow = .Range("A" & Rows).End(Excel.XlDirection.xlDown).Row
   lastCol = .Range("M" & "1").End(Excel.XlDirection.xlToRight).Column

   .Range(.Cells(1, lastCol), .Cells(1, Columns)).EntireColumn.Hidden = True
   .Range(lastRow & ":" & Rows).EntireRow.Hidden = True
   End With

   Thanks for your help