VBA & macros
Automate repeat work with macros and VBA code.
- Excel VBA Clear Array Contents: ReDim, Erase, and MoreIn Excel VBA, use Erase arrayName to clear an array: it deallocates a dynamic array, but resets a fixed-size array’s elements to…
- Excel VBA Clear Dictionary: Methods, Examples, and TipsTo empty a VBA Scripting.Dictionary, call its RemoveAll method: dict.RemoveAll. This removes every key and item while leaving the dictionary object available…
- Excel VBA Checkbox Loop: How to Read Every CheckboxTo loop through worksheet checkboxes in Excel VBA, use the worksheet’s CheckBoxes collection for Forms controls or its OLEObjects collection for ActiveX…
- Excel VBA: How to Cancel a UserFormTo cancel an Excel VBA UserForm, handle a Cancel button by setting a cancellation flag and hiding the form; handle the title-bar…
- Excel VBA: Change a PivotTable Data SourceTo change an Excel PivotTable’s source with VBA, create a new PivotCache from the replacement range, assign it with PivotTable.ChangePivotCache, then refresh…
- Excel VBA: How to Center a Range HorizontallyTo center the contents of every cell in an Excel range horizontally with VBA, set the range’s HorizontalAlignment property to xlCenter. For…
- Excel VBA Calendar Date Picker: Options and SetupExcel for Microsoft 365 on Windows does not include a universal VBA calendar date-picker control that you can simply add to any…
- Excel VBA: How to Cancel a LoopTo cancel a running Excel VBA loop on Windows, press Ctrl+Break; Esc may also work, especially when the code calls DoEvents. For…
- Excel VBA Bubble Sort for Arrays: Code and ExplanationTo bubble-sort a one-dimensional VBA array, compare neighboring items and swap them when they are out of order; repeat until a full…
- Excel VBA: Stop an Endless Loop and Recover SafelyTo stop a running Excel VBA loop on Windows, press Ctrl+Break (on some keyboards, use Ctrl+Fn+Pause), then choose End or Debug in…
- Excel VBA: How to Break Out of an Endless LoopTo stop a running Excel VBA loop on Windows, press Ctrl+Break (or Ctrl+Pause on keyboards with that key), then choose End to…
- Excel VBA Bubble Sort: Code, Steps, and AlternativesExcel VBA can sort a selected, single-column range of numbers with a bubble-sort macro: the code below repeatedly compares neighboring values and…
- Excel VBA Average Function: Syntax, Examples, and TipsIn Excel VBA, calculate an average by calling Excel’s built-in function: use Application.WorksheetFunction.Average(range) when you want a numeric result, or Application.Average(range) when…
- Excel VBA Binary Strings: Text, Bytes, and BitsIn Excel VBA, a String is text, not a raw sequence of binary bytes. If you mean a string of 0s and…
- Excel VBA AutoFilter: Select Visible Cells SafelyIn Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data range to get cells left visible by AutoFilter. Exclude the header from that…
- Excel VBA AutoFilter: Filter Rows by a Specific ValueTo filter a range by one exact value with Excel VBA, call AutoFilter on the range that includes the headers and set…
- Excel VBA AutoFilter Syntax: Fields, Criteria, and ExamplesIn Excel VBA, call AutoFilter on a range that includes the list’s header row; the usual syntax uses the named arguments Field,…
- Excel VBA AutoFilter Tables: Setup, Code, and Common FixesTo filter an Excel table with VBA, use the table’s Range.AutoFilter method and specify the column’s position within the table and the…
- Excel VBA AutoFilter: Show All DataTo show every row hidden by an Excel AutoFilter, use VBA’s ShowAllData method, but first check that the worksheet is actually filtered:…
- Excel VBA AutoFilter Operator ExplainedIn Excel VBA, the AutoFilter Operator argument tells Excel how to apply filter criteria: use xlAnd or xlOr to combine two conditions…
- Excel VBA AutoFilter Sort: Filter and Sort Data RowsTo filter and sort rows with Excel VBA, apply AutoFilter to the data range, then configure that range’s Sort object. For example,…
- Excel VBA AutoFilter: Exclude Multiple ValuesExcel VBA AutoFilter cannot use an array of “not equal to” criteria as a general exclusion list. An array passed with Operator:=xlFilterValues…
- Excel VBA AutoFilter Not Blank: Code and FixesTo show rows where a column is not blank, apply AutoFilter to the full table or data range and use the criterion…
- Excel VBA AutoFilter Off: Clear Filters or Remove ArrowsTo turn off a standard worksheet AutoFilter in Excel VBA, first clear any active filter so the filtered-out rows reappear, then set…
- Excel VBA AutoFilter Multiple Columns: Examples and TipsTo filter several Excel columns with VBA, call AutoFilter once for each field, using the same data range. Each call adds or…
- Excel VBA AutoFilter Multiple Criteria: A Practical GuideTo filter a range with Excel VBA using multiple allowed values in one column, pass an array to Criteria1 and set Operator:=xlFilterValues.…
- Excel VBA AutoFilter Multiple Fields: Examples and TipsTo filter multiple Excel fields with VBA, call AutoFilter on the same range once for each column, using that column’s Field number…
- Excel VBA AutoFilter Header: Set, Find, and Fix Header RowsIn Excel VBA, the AutoFilter header is the first row of the range you pass to AutoFilter; include that row in the…
- Excel VBA AutoFilter Exclude Criteria ExplainedTo exclude one exact value with Excel VBA AutoFilter, set the field’s criterion to a not-equal condition: Criteria1:="<>Closed". For example, dataRange.AutoFilter Field:=3,…
- Excel VBA AutoFilter Last Row: Find the Right RangeTo include every record in an Excel VBA AutoFilter, find the last data row and build the filter range from the header…