VBA & macros
Automate repeat work with macros and VBA code.
- Excel VBA AutoFilter: Filter Cells That Contain TextYes. In Excel VBA, use AutoFilter with asterisks around the text in the criterion: Criteria1:="*invoice*" returns rows whose filtered column contains “invoice”…
- Excel VBA AutoFilter Criteria: Syntax and ExamplesIn Excel VBA, set AutoFilter criteria with the Range.AutoFilter method: specify a filter range that includes its header, a Field number relative…
- Excel VBA AutoFilter with an Array: A Practical GuideYes. In Excel VBA, pass a one-dimensional array of the values you want to keep to the range’s AutoFilter method, using Operator:=xlFilterValues.…
- Excel VBA AutoFilter Array: Filter Multiple ValuesTo filter a column for several exact values with Excel VBA, pass an array to Criteria1 and set Operator:=xlFilterValues. For example, dataRange.AutoFilter…
- Excel VBA AutoFilter Based on a Cell Value: ExamplesYes. In VBA, pass the value of a worksheet cell to the Criteria1 argument of Range.AutoFilter, for example: filterRange.AutoFilter Field:=2, Criteria1:=criteriaCell.Value. Set…
- Excel VBA Array to Sheet: Write Data with Range.ValueTo write a VBA array to an Excel worksheet, assign a two-dimensional array to a range of the same size with the…
- Excel VBA Array Trim: Remove Blank ItemsIf you mean trimming an array in Excel VBA, VBA has no built-in function that removes blank elements from an array. The…
- Excel VBA ArrayList: How to Use It and When to Choose ItAn Excel VBA ArrayList is a resizable, zero-based list that can hold items and provides methods such as Add, Remove, Sort, and…
- Excel VBA Array to Collection: Methods and ExamplesTo convert a VBA array to a Collection, create a Collection and add each array element in a loop. For example, assign…
- Excel VBA Array Indexing: Bounds, Loops, and ExamplesAn Excel VBA array index is the number used to identify an element in an array. In VBA, an array declared without…
- Excel VBA Array of Worksheets: Create and Use OneIn Excel VBA, you can work with several worksheets as a group by passing an array of sheet names to Worksheets, or…
- Excel VBA Array Syntax: Declarations, Bounds, and ExamplesIn Excel VBA, declare an array with parentheses after its name, such as Dim scores(1 To 5) As Long; use Dim scores()…
- Excel VBA Array Example: Create, Fill, and Write DataA simple Excel VBA array example is to store values in a two-dimensional array, then write the array to a worksheet range…
- Excel VBA Array Formula: How to Enter and Manage ItIn Excel VBA, use Range.FormulaArray to enter a traditional, Ctrl+Shift+Enter array formula, or use Range.Formula2 to enter a modern dynamic-array formula that…
- Excel VBA Array Empty? How to Check If It Is AllocatedIn Excel VBA, IsEmpty does not reliably tell you whether an array has been allocated. For a dynamic array, test whether it…
- Excel VBA Array Count: Find the Number of ElementsIn Excel VBA, count the elements in an array with UBound(array) - LBound(array) + 1 for a one-dimensional array. Because arrays do…
- Excel VBA Arrays: How to Define and Use ThemAn Excel VBA array is a variable that holds multiple values under one name, with each value accessed by an index. Declare…
- Excel VBA Array Duplicate Values: Find, Keep, or RemoveYes. To find duplicate values in an Excel VBA array, scan its elements and track values already seen; to remove duplicates while…
- Excel VBA Array Clear: How to Erase or Reset ValuesTo clear a VBA array, use the Erase statement: Erase arrayName. For a fixed-size array, this resets its elements to their default…
- Excel VBA Arrays: What They Contain and How to Use ThemAn Excel VBA array contains an ordered set of elements that a macro can access by index; those elements can hold values…
- Excel VBA Array Append: Safe Methods and ExamplesTo append a value to a one-dimensional Excel VBA array, increase its upper bound with ReDim Preserve, then assign the new value…
- Excel VBA Array Add: Append Values with ReDim PreserveTo add an item to a VBA array, resize a dynamic array with ReDim Preserve, then assign the new value to its…
- Excel VBA AutoFilter: How to Apply Filters with CodeIn Excel VBA, apply AutoFilter to a range that includes its header row, then set the field number and criterion—for example, filter…
- Excel VBA Array: How to Add an ItemTo add an item to a one-dimensional dynamic VBA array, increase its upper bound with ReDim Preserve, then assign the new value…
- Excel VBA Application.InputBox Type: Values and UseIn Excel VBA, the Type argument of Application.InputBox controls what kind of response the dialog accepts and returns. For example, use Type:=1…
- Excel VBA: Add a Worksheet With a Specific Name SafelyTo add a worksheet with a specific name in Excel VBA, add the sheet to the intended workbook and then assign its…
- Excel VBA Array Allocation: Declare, Size, and ResizeIn Excel VBA, declare a dynamic array with empty parentheses, then allocate it with ReDim; for example, use Dim values() As Long…
- Excel VBA: Append a Worksheet to Another Workbook SafelyTo append a worksheet with Excel VBA, copy it after the destination workbook’s last sheet. The macro below copies the Data sheet…
- Excel VBA: Add Double Quotes to a String in VBA CodeTo add double quotes around a string in Excel VBA, concatenate a quote character before and after the text: Chr(34) & text…
- Excel VBA Dictionary: Add Items and Handle Existing KeysIn Excel VBA, add a key-item pair to a dictionary with its .Add method, such as dict.Add "North", 125. A dictionary has…