You can use the following methods to change the width of columns in Excel using VBA:
Method 1: Change Width of One Column
Sub ChangeColumnWidth()
Columns("B").ColumnWidth = 20
End Sub
This particular macro changes the width of column B to 20.
Note: The default width of columns in Excel is 8.29.
Method 2: Change Width of Multiple Columns
Sub ChangeColumnWidth()
Columns("B:D").ColumnWidth = 20
End Sub
This particular macro changes the width of all columns in the range from B to D to 20.
Method 3: Auto Adjust Width of Multiple Columns
Sub ChangeColumnWidth()
Columns("B:D").AutoFit
End Sub
This particular macro automatically adjusts the width of each column in the range from B to D to be as wide as necessary to display the longest cell in each column.
The following examples show how to use each of these methods in practice with the following dataset in Excel:
Example 1: Change Width of One Column
We can create the following macro to change the width of column B to 20:
Sub ChangeColumnWidth()
Columns("B").ColumnWidth = 20
End Sub
When we run this macro, we receive the following output:
Notice that only the width of column B (the “Points” column) has increased to 20 while the width of all other columns remained the same.
Example 2: Change Width of Multiple Columns
We can create the following macro to change the width of columns B through D to 20:
Sub ChangeColumnWidth()
Columns("B:D").ColumnWidth = 20
End Sub
When we run this macro, we receive the following output:
Notice that the width of each column from B to D has increased to 20 while the width of column A remained the same.
Example 3: Auto Adjust Width of Multiple Columns
We can create the following macro to automatically adjust the width of each column from A to D to be as wide as necessary to display the longest cell in each column.
Sub ChangeColumnWidth()
Columns("A:D").AutoFit
End Sub
When we run this macro, we receive the following output:
Notice that the width of each column has automatically been adjusted to be as wide as necessary to display the longest cell in each column.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Used Columns
VBA: How to Find Last Used Column
VBA: How to Delete Columns
How can I substitute “B” in the formula, Columns(“B”).ColumnWidth = 20, with a Named Field so I can easily change which columns I want to change the width. For example, If I had a cell with a Named Field call “I_change_width”. Then I can put in that field D:D, then that will get passed to the formula, Columns(“I_change_width”).ColumnWidth = 20.
Hi Ron…To change the column width dynamically based on a named range in VBA, you can use the `Range` object to get the value of the named range and then use that value to set the column width. Here’s how you can do it:
### Step-by-Step Guide
1. **Create a Named Range**:
– Select the cell where you want to specify the column address (e.g., `D:D`).
– Go to the `Formulas` tab, click on `Define Name`, and name it `I_change_width`.
2. **VBA Code**:
– Open the VBA editor by pressing `Alt + F11`.
– Insert a new module by right-clicking on any existing module or `VBAProject` and selecting `Insert > Module`.
– Write the following code in the module:
“`vba
Sub ChangeColumnWidth()
Dim colAddress As String
Dim namedRange As Range
‘ Get the named range
Set namedRange = ThisWorkbook.Names(“I_change_width”).RefersToRange
‘ Get the value from the named range (should be something like “D:D”)
colAddress = namedRange.Value
‘ Set the column width
Columns(colAddress).ColumnWidth = 20
End Sub
“`
### Explanation:
1. **Named Range**: The named range `I_change_width` holds the column reference (e.g., `D:D`).
2. **VBA Code**:
– `Set namedRange = ThisWorkbook.Names(“I_change_width”).RefersToRange`: This line gets the range that the named range refers to.
– `colAddress = namedRange.Value`: This retrieves the value from the named range, which should be something like `D:D`.
– `Columns(colAddress).ColumnWidth = 20`: This sets the column width of the specified column.
### How to Run the Macro:
1. Make sure the column reference (e.g., `D:D`) is entered in the named range `I_change_width`.
2. Run the `ChangeColumnWidth` macro by pressing `F5` in the VBA editor or by going to the `Developer` tab in Excel and selecting `Macros`, then running `ChangeColumnWidth`.
This way, you can dynamically change the column width based on the value specified in a named field, making it easy to adjust which column’s width you want to change without modifying the VBA code.
Hi, I have been able to successfully run this macro to resize my table columns. I am also able to run a macro to refresh the table from the source data upon opening the workbook.
However, when I try to run both macros together, the refresh one works, but the resizing one doesn’t. Any advice?
This is the code for what I am running:
Private Sub workbook_open()
Call refresh
Call resize
End Sub
Hi Patricia…It looks like you’re calling two different macros (`refresh` and `resize`) in your `workbook_open` event. When the workbook opens, both should run in sequence. However, if one works (the refresh) and the other doesn’t (the resize), it could be due to timing issues or the order of operations.
Here are some steps you can take to troubleshoot and fix the issue:
### 1. **Check for Errors in the `resize` Macro**
Ensure that the `resize` macro itself works independently by running it manually. This will confirm that there are no issues within the code logic of the `resize` macro.
### 2. **Add a Delay or Wait Command**
The `refresh` process may take some time to complete, and if the `resize` macro is triggered before the refresh is fully done, it could be causing an issue. You can add a small delay to allow the refresh to complete before resizing the columns.
Here’s how you can add a delay between the two macros:
“`vba
Private Sub workbook_open()
Call refresh
Application.Wait (Now + TimeValue(“0:00:02”)) ‘ 2-second delay
Call resize
End Sub
“`
This adds a 2-second wait after the refresh. Adjust the time if needed.
### 3. **Use `DoEvents` to Yield Control**
Another approach is to use `DoEvents` to yield control back to the system, allowing Excel to process the refresh operation completely before moving on to resize the columns.
Here’s an updated version of your code using `DoEvents`:
“`vba
Private Sub workbook_open()
Call refresh
DoEvents ‘ Allow Excel to process other events
Call resize
End Sub
“`
### 4. **Ensure Proper Event Trigger**
Ensure that the `resize` macro is not dependent on the `refresh` macro’s data being fully updated in a way that the refresh needs to be completed before the resize. You may want to place the `resize` code at the end of the `refresh` macro to ensure it runs only after the refresh is fully completed:
“`vba
Sub refresh()
‘ Your refresh code here
‘ Resize after refresh completes
Call resize
End Sub
“`
### 5. **Recheck Table References**
Make sure that the `resize` macro is referring to the correct table or range. Sometimes if the range or table isn’t yet fully loaded after a refresh, the resize operation might fail.
### Conclusion
If adding a delay, using `DoEvents`, or combining the macros resolves the issue, it’s likely a timing problem. If none of these approaches work, there may be another issue with how the table data or references are being handled in your code.
Let me know if these solutions help!