CODEWITHSUNDEEP
vbaexcel
Python learner 93
Python learner 93

Reputation: 105

How would I go about autosizing columns in an Excel sheet only up to a certain row in VBA?

I am very new to VBA and have an Excel worksheet that includes a data table, in addition to some notes at the bottom. I only want to autosize the columns of the data table, but not the notes. Is there a way to autosize columns, but only up to a specified row?

Upvotes: 2

Views: 98

Answers (3)

Fabien Bouleau
Fabien Bouleau

Reputation: 464

When you can perform what you want manually, you may simply record a macro and check the code Excel has generated. It helped me out many times.

In your case, supposing you want to autosize cells A1 to A3 only, start the macro recording, select cells A1:A3 and select menu Home > Format > AutoFit Column Width... You then obtain the following code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:A3").Select
    Selection.Columns.AutoFit
End Sub

It is certainly not perfect, but it is a good start to learn. Excel is sometimes exhaustive (especially with the SaveAs feature), but you can then use the help to understand the options and rule out the ones you don't need.

Upvotes: -1

mooseman
mooseman

Reputation: 2017

What might be easier and faster is to determine you range without the notes and autofit the column width

Range("A1:A3").Columns.AutoFit

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27259

Below is a way around it through VBA. Mine is a simplistic example that deletes the note cells from the sheet, AutoFits the columns, then puts notes back in place.

Option Explicit

Sub AutoSizeExclNotes()

    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Sheet1")

    With ws1

        Dim sNotes As String
        sNotes = .Range("B6").Value2

        With .Range("B6")
            .ClearContents
            .EntireColumn.AutoFit
            .Value = sNotes
        End With

    End With

End Sub

Before Before

After

After

Upvotes: 1

Related Questions