VBA & macros
Automate repeat work with macros and VBA code.
- VBA Close Workbook: Save, Close, and Avoid Common ErrorsTo close a workbook with VBA, call its Close method: use Workbooks("Report.xlsx").Close SaveChanges:=True for a named workbook, or ThisWorkbook.Close SaveChanges:=False for the…
- VBA: Close a UserForm with a Command ButtonTo close an Excel VBA UserForm when someone clicks a command button, put Unload Me in that button’s Click event procedure. Use…
- VBA: Clear an AutoFilter If It Exists SafelyYes. In Excel VBA, check whether the worksheet is currently filtered with Worksheet.FilterMode, then call ShowAllData only when it is: If ws.FilterMode…
- VBA Change Worksheet Name: Code and Common IssuesTo change a worksheet name with VBA, assign a new value to the worksheet’s Name property, such as Worksheets("Sheet1").Name = "Summary". In…
- VBA Change Pivot Table Source: Code and TipsTo change a PivotTable’s source with VBA, create a PivotCache for the new worksheet range or Excel table, assign it with PivotTable.ChangePivotCache,…
- VBA Cell to Range: How to Reference Cells in ExcelIn Excel VBA, a cell is already a one-cell Range object: use Set target = Worksheets("Sheet1").Range("A1") to store a reference to cell…
- VBA Dictionary Values: How to Change an Item SafelyYes. In VBA, change a Dictionary value by assigning a new value to its key, for example dict("North") = 135; you can…
- Can VBA Create a Pivot Table? Steps and CodeYes. VBA can create an Excel PivotTable by building a PivotCache from a source range and adding a PivotTable to a worksheet.…
- VBA Bubble Sort: How It Works and When to Use It in ExcelA VBA bubble sort repeatedly compares neighboring values and swaps them when they are out of order, until the list is sorted.…
- VBA Calendar UserForm: Build a Date Picker in ExcelA VBA calendar UserForm is a custom date picker that lets someone choose a date inside an Excel form instead of typing…
- VBA Break Workbook Links: What the Macro Actually DoesYes. Excel VBA can break external workbook links: Excel replaces formulas that depend on linked workbooks with their current calculated values, so…
- VBA AutoFilter: What True Means and How to Use ItYes, VBA AutoFilter uses True in some related settings, but True is not a universal command to filter rows. In Excel VBA,…
- VBA AutoFilter for Tanaka: Filter Excel Rows by NameTo show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header row, then set the…
- VBA AutoFilter Syntax: Criteria, Fields, and ExamplesIn Excel VBA, the basic AutoFilter syntax is Range.AutoFilter Field:=columnNumber, Criteria1:=value. The range should include the list’s header row, and Field is…
- VBA AutoFilter Sort: Filter and Sort Rows in ExcelTo filter and sort rows with VBA, apply an AutoFilter to the data range, then use the worksheet’s Sort object to set…
- Excel VBA AutoFilter and Sort: Apply Both to Data SafelyYes. In Excel VBA, apply AutoFilter to a range that includes its headers, then sort that same complete range with the worksheet’s…
- VBA AutoFilter Operator: Values, Uses, and ExamplesIn VBA, the Operator argument of Range.AutoFilter tells Excel how to combine two criteria for one field or which special filter mode…
- VBA AutoFilter Not Working? Common FixesIf VBA AutoFilter is not working, first check that the macro targets the correct worksheet and a contiguous range with a header…
- VBA AutoFilter Not Blank: Filter Nonempty CellsTo use VBA AutoFilter to show only nonblank cells, set the field’s criterion to <>: dataRange.AutoFilter Field:=2, Criteria1:="<>". The field number is…
- VBA AutoFilter Header: How to Set the Header Row in ExcelIn VBA, the first row of the range you pass to AutoFilter is treated as the header row; there is no Header:=…
- VBA AutoFilter Field Criteria1: Values, Operators & ExamplesIn Excel VBA, Field identifies which column within the filtered range to filter, while Criteria1 supplies the first condition to match. For…
- VBA AutoFilter: What It Filters and How to Use ItVBA AutoFilter filters the rows in an Excel list or table according to criteria in one or more columns, hiding rows that…
- VBA AutoFilter Exclude Criteria: How to Filter Out ValuesTo exclude a value with VBA AutoFilter, use a not-equal criterion such as <>Closed: filterRange.AutoFilter Field:=3, Criteria1:="<>Closed". The field number is the…
- VBA AutoFilter Date: Filter Dates ReliablyTo filter dates with VBA, apply AutoFilter to a range that includes its header row, then set date criteria on the date…
- VBA AutoFilter Dynamic Range: A Reliable SetupTo apply VBA AutoFilter to a range that changes size, calculate the range’s last row (and, if needed, last column) before calling…
- VBA AutoFilter Criteria Not Equal: How to Use ItTo filter out a value with VBA AutoFilter, pass a not-equal criterion such as "<>Closed" to the field you want to filter:…
- VBA AutoFilter Criteria1: Syntax, Examples & Tips in ExcelIn VBA, Criteria1 is the first filter condition passed to Excel’s AutoFilter method; for example, Criteria1:="East" keeps rows whose selected field contains…
- VBA AutoFilter Criteria Array: Syntax and ExamplesTo filter a field for several allowed values in VBA, pass a Variant array to Criteria1 and set Operator:=xlFilterValues. For example, dataRange.AutoFilter…
- VBA AutoFilter Count: Count Visible Rows in ExcelTo count data rows visible after an Excel AutoFilter, count the visible cells in one column of the filter’s data range, excluding…
- VBA AutoFilter Criteria: Examples and Common FixesIn VBA, AutoFilter criteria are the conditions you pass to the AutoFilter method to show matching rows. For example, use Criteria1:="West" to…