CODEWITHSUNDEEP
vb.netexcel
user47368
user47368

Reputation: 59

Formatting all cells on a spreadsheet to the same designated height and width

I'm trying to format an entire Excel worksheet to have the same width and height for all cells.

The following code does not work, but does not give an error either:

        transposeSheet.Cells.UseStandardHeight = 15
    transposeSheet.Cells.UseStandardWidth = 15

There is a lot of code out there for doing individual ranges, but I cannot find how to do the entire sheet to a specified height and width.

Upvotes: 0

Views: 76

Answers (2)

Karen Payne
Karen Payne

Reputation: 5117

Note I just created a MSDN code sample on the following.

Here is an example that ensures that all objects are disposed of properly which is why there is a fair amount of code else we could do this with less code where many gleam off the web that will do the work but leave remnants of un-disposed objects in computer memory sometimes if lucky will be disposed of when the app closes other times not.

enter image description here

Call example

Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WorksheetsTest.xlsx")
Dim SheetName As String = "Sheet1"

SetWidthHeight(FileName, SheetName, 10, 50)

Code module for above

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module OpenWorkSheets4
    Public Sub SetWidthHeight(ByVal FileName As String, ByVal SheetName As String, ByVal RowHeight As Integer, ByVal ColumnHeight As Integer)
        If IO.File.Exists(FileName) Then
            Dim Proceed As Boolean = False

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False

            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then
                xlCells = xlWorkSheet.Cells
                Dim EntireRow As Excel.Range = xlCells.EntireRow
                EntireRow.RowHeight = RowHeight
                EntireRow.ColumnWidth = ColumnHeight

                ReleaseComObject(xlCells)
                ReleaseComObject(EntireRow)
            Else
                MessageBox.Show(SheetName & " not found.")
            End If

            xlWorkSheet.SaveAs(FileName)

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located.")
        End If
    End Sub
    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Module

Upvotes: 1

user4099136
user4099136

Reputation:

You'll want to use the below

Cells.Select // Selects all cells on active sheet
Selection.RowHeight = Yournumberhere // Changes height of selected rows
Selection.ColumnWidth = Yournumberhere // Changes width of selected columns

Upvotes: 0

Related Questions