AutoFit Cell via VBA?

R

Restricted

Board Regular
Joined
Aug 9, 2014
Messages
76
How would you best go about auto fitting column width?

I am using a sub procedure to insert new row(s), contingent on the user's input, into a worksheet, however it would be nice to have the data to be completely visible after the row(s) has been inserted.
I suppose this also needs to be done every time a new row is inserted, rather than just once, blatantly because new data could be longer than previous data entered.

So far, I have thought to use this:
Columns().AutoFit

It also appears as though both of these are different in nature, how do I be judicious about which to use?

Columns("A:H").AutoFit
Range("A1:H1").Columns.AutoFit

I have also heard the use of the Excel VBA 'EntireRow' property, would this help in any way?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
I tend to use:
Code: 
Range("A1:H1").EntireColumn.AutoFit
For me the preference is because I know what cell I'm applying it to (EntireColumn refers to the entire column of the cell in question, so row number is irrelevant) and because I tend to use keyword Range most of the time when referencing a cell object, using Range("A1").EntireColumn is just a continuation of this, so fits my style.
 
Last edited:
Upvote 0
JackDanIce said:
I tend to use:
Code: 
Range("A1:H1").EntireColumn.AutoFit
For me the preference is because I know what cell I'm applying it to (EntireColumn refers to the entire column of the cell in question, so row number is irrelevant) and because I tend to use keyword Range most of the time when referencing a cell object, using Range("A1").EntireColumn is just a continuation of this, so fits my style.
Click to expand...

Ok thanks, so that should work fine even if new (longer) text is added as a row?
That is, I heard that it only auto fits based only on the contents of cells A1:H1, rather than the entire A:H column. (Disregard this, I thought this would be the case according to what it stated on the Microsoft website)

Furthermore, wouldn't EntireRow apply the AutoFit to ALL the columns? That's essentially what I want. i.e. 10 columns, need them ALL to be automatically fitted (by column width).
i.e. Maybe that was a bad example, but why doesn't EntireRow just reinforce that it will apply the AutoFit to the entire row (i.e. all columns)? In other words, why doesn't EntireRow work? (I tried and it didn't)

Thanks!
 
Last edited:
Upvote 0
I believe when you use EntireColumn it is for rows 1 to the very last row, for the column specified. So if you have Range("A1:H1").EntireColumn, it is for the columns that A1:H1 are part of inclusive (that is A1, B1, C1, ... H1). This means no matter where in columns A:H you add data, Range("A1:H1").EntireColumn.Autofit, will fit the respective columns to the size of the largest cell contents found in the given column.

EntireRow is the tranpose of EntireColumn. You may be better off trying some simple examples (on a new worksheet) to get the idea of this:
Code: 
Sub Test_AutoFit ()

Range("A40").Resize(,8).Value = 1 'Populating values in row 40
Range("A1:D1").EntireColumn.AutoFit 'Autosizing column widths
MsgBox "Review column widths for A:D"
Range("E1:H1").EntireColumn.AutoFit
MsgBox "Review column widths for E:H"
Range("A1:H1").EntireColumn.Delete

End Sub
 
Upvote 0
JackDanIce said:
I believe when you use EntireColumn it is for rows 1 to the very last row, for the column specified. So if you have Range("A1:H1").EntireColumn, it is for the columns that A1:H1 are part of inclusive (that is A1, B1, C1, ... H1). This means no matter where in columns A:H you add data, Range("A1:H1").EntireColumn.Autofit, will fit the respective columns to the size of the largest cell contents found in the given column.

EntireRow is the tranpose of EntireColumn. You may be better off trying some simple examples (on a new worksheet) to get the idea of this:
Code: 
Sub Test_AutoFit ()

Range("A40").Resize(,8).Value = 1 'Populating values in row 40
Range("A1:D1").EntireColumn.AutoFit 'Autosizing column widths
MsgBox "Review column widths for A:D"
Range("E1:H1").EntireColumn.AutoFit
MsgBox "Review column widths for E:H"
Range("A1:H1").EntireColumn.Delete

End Sub
Click to expand...

Thanks I understand now.

In regards to your example however, what is the point it is supposed to demonstrate?
Line 4 and Line 6 appears to have the same result (column widths change exactly the same)
Is Line 6 supposed to use EntireRow instead? I attempted that, and it basically does nothing, how should I see it?
 
Upvote 0
Simply meant to show, in a new worksheet, when the code enters a value of 1 into A40:H40, then using Range("A1:H1").EntireColumn.AutoFits it to the width of the values in row 40 (because on a new sheet, these are the only cells that should contain any values after running this macro).

Range("A1:D1").EntireColumn.AutoFit is meant to show you that with values in A40:H40, the autofit can be done ONLY on columns A:D, referencing A1:D1.

EntireRow works in a similar principal to EntireColumn except for rows. I haven't provided an example for this or intended to use EntireRow anywhere in my suggestion.
 
Upvote 0
JackDanIce said:
Simply meant to show, in a new worksheet, when the code enters a value of 1 into A40:H40, then using Range("A1:H1").EntireColumn.AutoFits it to the width of the values in row 40 (because on a new sheet, these are the only cells that should contain any values after running this macro).

Range("A1:D1").EntireColumn.AutoFit is meant to show you that with values in A40:H40, the autofit can be done ONLY on columns A:D, referencing A1:D1.

EntireRow works in a similar principal to EntireColumn except for rows. I haven't provided an example for this or intended to use EntireRow anywhere in my suggestion.
Click to expand...

Great thanks. However I realised you mentioned using Range("A1:H1").EntireColumn.AutoFits. No where in your code is that particular code, is this what you intended?
Basically I am just unsure as to the purpose of having BOTH

Range("A1:D1").EntireColumn.AutoFit
AND
Range("E1:H1").EntireColumn.AutoFit

where the difference is only in the range. Hence I thought you were trying to demonstrate something DIFFERENTLY in each of the 2 cases above. Are you?

Thank you!
 
Upvote 0
No, it was just to show how you can use Range property to break up areas and apply autofits as your code requries, hence using MsgBox so as you run the code, you can see the results update on the screen to hopefully understand what each line of code achieves.

Fundamentally, there is no difference between Range("A1").EntireColumn.AutoFit and Range("A1:Z1").EntireColumn.AutoFit, apart from the address value you provide to Range().
 
Upvote 0
JackDanIce said:
No, it was just to show how you can use Range property to break up areas and apply autofits as your code requries, hence using MsgBox so as you run the code, you can see the results update on the screen to hopefully understand what each line of code achieves.

Fundamentally, there is no difference between Range("A1").EntireColumn.AutoFit and Range("A1:Z1").EntireColumn.AutoFit, apart from the address value you provide to Range().
Click to expand...

Got it thanks!
And I think what also led to further confusion was my understanding of EntireColumn. So EntireColumn actually attains the address value of EACH column specified? Initially I had thought "EntireColumn" almost sounds like it attains only the address value of a SINGLE entire column (i.e. C:C), this is incorrect? and the former is correct?
 
Upvote 0
EntireColumn references against the addresses passed to the Range argument, it is not limited to a single column, the example above with lines
Code: 
Range("A40:H40").Value = 1
Range("A1:D1").EntireColumn
I hoped demonstrated this, when ONLY columns A-D inclusive autofit (autosized on the screen) before you clicked "Ok" on the message box. Perhaps if you use F9 and run through the code, 1 line at a time, you'll have a better understanding of it?

Range("A1:H1") will be all cells inclusive between A1:H1
Range("A1,C1,D1") would be just cells A1,C1 and D1 so Range("A1,C1,D1").EntireColumn would only be columns A, C and D.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA to AutoFit Row Height and AutoFit Column Width
2
Replies
10
Views
1K
Alex Blakenburg
A
E
  • Question
Setting multiple row heights in VBA
Replies
2
Views
174
Extivalis
E
C
  • Question
Delete Cell Range, or Add Range and Copy Formulas etc. - Excel VBA
Replies
0
Views
60
CSTjr
C
A
  • Question
autofit row by VBA
Replies
1
Views
458
rlv01
R
T
  • Question
How do I make Autofit expand a row to fit wrapped text that's more than one line?
Replies
0
Views
1K
TMan293
T

Forum statistics

Threads
1,214,833
Messages
6,121,861
Members
449,052
Latest member
Fuddy_Duddy

Share this page

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
Back
Top