Add in an autofit

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,228
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have some code that works perfectly. Only issue is that I want to add an autofit feature in. This is my code. WsTrack is a monthly sheet that entries are copied to and I want to autofit columns A:H in each instance of WsTrack at the end of the copy part of my procedure so that it doesn't autofit on every interation, just once at the end.

I get the error when I try and run it, Object doesn't support this property or method.
VBA Code:
With wsTrack
           lasttrack = .Cells(Rows.Count, "A").End(xlUp).Row + 1
              'this copies the date column in the tblCosting
           ' tblrow.Range(, 1).Copy
            'this pastes it into column A of report tracking file
            '.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
            .Range(.Cells(lasttrack, 1), .Cells(lasttrack, 1)) = inarr(i, 1)
            'this copies the YP name column in the tblCosting
'            tblrow.Range(, 4).Copy
            'this pastes it into column B of the report tracking file
            out1(1, 1) = inarr(i, 4)
'            .Range(.Cells(lasttrack, 2), .Cells(lasttrack, 2)) = inarr(i, 4)
               'this copies the YP name column in the tblCosting
'            tblrow.Range(, 5).Copy
            'this pastes it into column A of report tracking file
            out1(1, 2) = inarr(i, 5)
            .Range(.Cells(lasttrack, 2), .Cells(lasttrack, 3)) = out1 ' this saves 1 workhseet access
          
            lrTrack = wsTrack.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
                wsTrack.Sort.SortFields.Clear
                wsTrack.Sort.SortFields.Add Key:=Range("A2:A" & lrTrack), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

                        Columns("A:H").Select
                        Columns("A:H").EntireColumn.AutoFit

                        With Workbooks(ReportTracking).Worksheets(Combo).Sort
                            'set range to sort of A3 to AO
                            .SetRange Range("A1:I" & lrTrack)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With
          
        End With

What is the syntax to do this with autofit?
 
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,178
Office Version
  1. 2016
Platform
  1. Windows
That Columns("A:H").EntireColumn.AutoFit will AutoFit each column from A to H. Try remove

Columns("A:H").Select

You do not need to select to perform AutoFit
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,228
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I can't test it at the moment but I was wondering, is it in the right spot?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,178
Office Version
  1. 2016
Platform
  1. Windows
I can't test it at the moment but I was wondering, is it in the right spot?
The syntax is correct. I have my code using AutoFit just like the way you wrote it.

Once you have string filled in those column, each column will autofit accordingly.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,228
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I will test it when I am next at work.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,208
Messages
5,640,853
Members
417,174
Latest member
diegomuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top