What is Excel VBA Columns Property?
The Excel VBA Columns property refers to a collection of all the columns in a worksheet. It allows you to access and manipulate individual columns within the worksheet using VBA. The VBA Columns property is a member of the Worksheet object in VBA and provides various methods and properties to work with columns.
For example, you can use the VBA Columns property if you have data in the worksheet and want to change the font or color of only selected rows or columns.
Table of contents
Key Takeaways
- The Columns property in Excel VBA refers to all the columns in a worksheet.
- The property provides various methods to work with columns.
- You can add or insert columns using the Insert method of the “Columns” property, specifying the position and number of columns.
- Hide columns using the Hidden property, delete columns using the “Delete” method, and perform text-to-columns using the “TextToColumns” method.
How to Add VBA Columns?
To add VBA Columns:
- Identify the worksheet where you want to add columns.
- Use the “Columns” property of the worksheet to specify the range of columns you want to add.
- Use the VBA Columns Insert method on the specified range of columns.
- Optionally, specify the “Shift” parameter to determine the direction in which the existing columns should shift to accommodate the new columns.
Shift specifies how the existing columns should shift to accommodate the new ones. It can take one of the following values:
- Optionally, specify the “CopyOrigin” parameter to determine from which column the formatting and contents of the inserted columns should be copied.
“CopyOrigin” specifies from which column the formatting and contents of the inserted columns should be copied. It can take one of the following values:
Let us understand this further using the following examples.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
Let us look at some examples of using VBA Columns in different scenarios.
Example #1 – VBA Columns Width Property
This example demonstrates a simple use of the VBA Columns and VBA Columns Width property to insert a column and set its width. Some columns in a worksheet are populated with the text “ABCD.”
- Step 1: Open the Visual Basic Editor (VBE). To open the VBE, press the Alt + F11 keys in Excel or click on the Developer tab in the Excel ribbon and then click the Visual Basic button.
- Step 2: Create a new module. In the VBE, click the Insert menu and select Module to create a new module.
- Step 3: Start the subroutine “InsertColumnAndSetWidth().”
- Step 4: Declare a variable and set the worksheet reference.
The first statement above declares a variable named ‘ws’ to hold a reference to a worksheet object.
The second statement assigns the worksheet named “Example_1” to the ws variable. This refers to the worksheet where the code will be executed.
- Step 5: Insert a column and specify the shift and copy origin:
The above statement inserts a new column at column B in the worksheet specified by ws. The Shift argument is set to xlToRight, indicating that existing columns will shift to the right to make room for the inserted column.
The CopyOrigin argument is set to xlFormatFromLeftOrAbove, which means the formatting and contents of the inserted column will be copied from the column to the left or above.
- Step 6: Set the column width.
ws.Columns(“B”).ColumnWidth = 15 sets the width of column B in the worksheet specified by ws to 15 units. This step adjusts the width of the newly inserted column.
- Step 7: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “InsertColumnAndSetWidth(),” and run the code.
- Step 8: Once you run the code, it inserts a new column, Column B, in the specified worksheet, shifts existing columns to the right, copies the formatting and contents from the column to the left or above, and sets its width to 15 units.
Here is the full code:
Sub InsertColumnAndSetWidth()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Example_1”)
ws.Columns(“B”).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Columns(“B”).ColumnWidth = 15
End Sub
Example #2 – Select Column Based on Variable Value
This example shows how to select a column based on a variable value. Below is the dataset in a worksheet.
- Step 1: Create a new module and start the “SelectColumnBasedOnVariable ” subroutine.”
- Step 2: Declare variables and set the worksheet reference.
- Statement 1 above declares a variable ‘ws’ to hold a reference to a worksheet object.
- Dim columnName As String declares a variable named columnName to store the column’s name.
- Then, we declare a variable named selectedColumn in the third statement to hold the selected column range.
- The Set statement assigns the worksheet “Example_2” to the ws variable. It refers to the worksheet where the code will be executed.
- Step 3: Set the column name.
columnName = “C” assigns the value “C” to the columnName variable. This specifies the name of the column to be selected.
- Step 4: Select the column based on the variable.
This line selects the column specified using the “columnName” variable in the worksheet specified by ws. It assigns the selected column range to the “selectedColumn” variable.
- Step 5: Set the column color.
The statement below sets the interior color of the selected column to green. The RGB function is used to specify the color value (0 for red, 255 for green, and 0 for blue), resulting in a green color for the column.
- Step 6: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu and select “SelectColumnBasedOnVariable().” Now, run the code..
- Step 7: Once you run the code, it selects a column in the specified worksheet based on the value stored in the “columnName” variable and sets the interior color of the selected column to green.
Here is the full code:
Sub SelectColumnBasedOnVariable()
Dim ws As Worksheet
Dim columnName As String
Dim selectedColumn As Range
Set ws = ThisWorkbook.Worksheets(“Example_2”)
columnName = “C”
Set selectedColumn = ws.Columns(columnName)
‘ Set the column color to green
selectedColumn.Interior.Color = RGB(0, 255, 0)
End Sub
Example #3 – Select Column Based on Cell Value
This example demonstrates how to select a column based on the value in a specific cell. Please refer to Module 3 in the “VBA Columns” worksheet.
- Step 1: Create a new module and start the subroutine “SelectColumnBasedOnCellValue().”
- Step 2: Declare variables and set the worksheet reference.
- Statement 1 above declares a variable named ws to hold a reference to a worksheet object.
- Statement 2 declares a variable named searchValue to store the value to search for.
- Statement 3 declares a variable named searchRange to specify the range of the search.
- Statement 4 declares a variable named foundColumn to hold the range of the column where the search value is found.
- Set ws = ThisWorkbook.Worksheets(“Example_3”) assigns the worksheet named “Example_3” to the ws variable. This refers to the worksheet where the code will be executed.
- Step 3: Set the search parameters.
- searchValue = “Search Text” assigns the value “Search Text” to the searchValue variable. It specifies the value to search for.
- Set searchRange = ws.Range(“A1:D1”) assigns the range from cell A1 to D1 in the specified worksheet to the searchRange variable. It defines the range where the search will be performed.
- Step 4: Perform the search.
The line of code uses the Find method on the “searchRange” to search for the “searchValue.”
The “What” parameter specifies the value to search for, “LookIn” specifies to search in cell values, and “LookAt” specifies to match the entire cell value. The result is assigned to the “foundColumn” variable.
- Step 5: Select the corresponding column and check if the value is found.
- The IF statement in excel checks if a match was found based on the search value. If the foundColumn is not Nothing, it means a match was found.
- foundColumn.EntireColumn.Select selects the entire column of the found cell using the EntireColumn property. It selects the column where the search value is found.
- Step 6: Save the module and close the VBA. Now, open the Macro menu and select “SelectColumnBasedOnCellValue(),” and run the code,
- Step 7: Once you run the code, it searches for a specific value within a given range in the worksheet. If the value is found, it selects the entire column of the found cell.
Here is the full code:
Sub SelectColumnBasedOnCellValue()
Dim ws As Worksheet
Dim searchValue As String
Dim searchRange As Range
Dim foundColumn As Range
Set ws = ThisWorkbook.Worksheets(“Example_3”)
searchValue = “Search Text”
Set searchRange = ws.Range(“A1:D1”)
‘ Search for the value in the searchRange
Set foundColumn = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
‘ Check if the value is found and select the corresponding column
If Not foundColumn Is Nothing Then
foundColumn.EntireColumn.Select
End If
End Sub
Example #4 – Combination of Range & Column Property
This example combines the Range and Column properties to insert columns within a specified range.
- Step 1: Create a new module and start the subroutine, “InsertColumnsInRange().”
- Step 2: Declare variables and set the worksheet reference:
- The Dim statement declares a variable named ws to hold a reference to a worksheet object and a variable named rng to specify the range where the columns will be inserted.
- The Set statement assigns the worksheet “Example_4” to the ws variable.
- Step 3: Specify the range where you want to insert columns:
Here, the VBA Set statement assigns the range C2:F10 in the specified worksheet to the rng variable. It defines the range where the columns will be inserted.
- Step 4: Insert the columns within the specified range.
The above line inserts new columns within the rng range. The Shift argument is set to xlToRight, indicating that existing columns will shift to the right to accommodate the new columns.
The CopyOrigin argument is set to xlFormatFromLeftOrAbove, which means the formatting and contents of the inserted columns will be copied from the columns to the left or above.
- Step 5: Save the module and close the VBA. Open the Macro menu, select “InsertColumnsInRange(),” and run the code.
- Step 6: It inserts new columns within the specified range in the worksheet. Existing columns shift to the right, and the formatting and contents of the inserted columns are copied from the columns to the left or above the insertion point.
Here is the full code:
Sub InsertColumnsInRange()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets(“Example_4”)
‘ Specify the range where you want to insert columns
Set rng = ws.Range(“C2:F10”)
‘ Insert the columns within the specified range
rng.Columns.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Example #5 – Select Multiple Columns with Range Object
This example demonstrates how to select multiple columns using the Range object. We use the same data as the previous examples.
- Step 1: Create a new module and start the subroutine “SelectMultipleColumns().”
- Step 2: Declare variables and set the worksheet reference as in the previous examples.
- Set ws = ThisWorkbook.Worksheets(“Example_5”) assigns the worksheet named “Example_5” to the ws variable. This refers to the worksheet where the code will be executed.
- Step 3: Select the columns to be formatted.
The Set statement assigns the range containing columns B, D, and F in the specified worksheet to the selectedRange variable.
- Step 4: Format the selected columns.
selectedRange.Font.Color = RGB(0, 0, 255) sets the font color of the selected columns to blue. The RGB function is used to specify the color value (0 for red, 0 for green, and 255 for blue), resulting in a blue font color for the selected columns.
- Step 5: Save the module and close the VBA. Now, open the Macro menu and select “SelectMultipleColumns()” and run.
- Step 6: Once you run the code, it selects columns B, D, and F in the specified worksheet and sets their font color to blue.
Here is the full code:
Sub SelectMultipleColumns()
Dim ws As Worksheet
Dim selectedRange As Range
Set ws = ThisWorkbook.Worksheets(“Example_5”)
‘ Select columns B, D, and F
Set selectedRange = ws.Range(“B:B,D:D,F:F”)
‘ Format the selected columns by setting font color to blue
selectedRange.Font.Color = RGB(0, 0, 255)
End Sub
Important Things to Note
- The columns can be referenced using either the column letter or the column index number.
- The “Columns” property can be combined with other VBA functions and properties, such as Range, to perform advanced operations on columns.
- The VBA Columns Autofit method can automatically adjust the columns’ width to fit the contents within them. It adjusts the column width based on the widest content present in the column.
Frequently Asked Questions (FAQs)
You can use the Hidden property of the Columns object to hide columns using VBA. For example:
ws.Columns(“A:C”).Hidden = True
To delete columns, you can use the VBA Columns Delete method of the Columns object. For example:
ws.Columns(“D:E”).Delete
To perform Text to Columns in VBA, use the “TextToColumns” method on a range. Specify the range you want to split and set the appropriate parameters such as “Destination,” “DataType,” and the delimiter options like “Comma” or “Tab.”
This method will split the selected range into multiple columns based on the chosen delimiter.
To loop through columns in VBA, use a “For Each” loop with a worksheet’s “Columns” property, iterating through each column to perform desired actions or access specific properties.
Download Template
This article must be helpful to understand the VBA Columns, with its property and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Columns. Here we learn how to access & change property of columns using VBA code in excel with examples & downloadable template. You can learn more from the following articles –
Leave a Reply