VBA & macros
Automate repeat work with macros and VBA code.
- VBA Code to Delete Rows in Excel: Practical ExamplesIn Excel VBA, delete a worksheet row with Rows(rowNumber).Delete; when removing rows that meet a condition, loop from the bottom upward so…
- VBA Code to Delete a Row in ExcelTo delete a worksheet row with VBA, use Rows(5).Delete for row 5. In a macro, qualify the row with the worksheet you…
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to use as the destination,…
- VBA Code to Copy a Range in ExcelIn Excel VBA, copy a range by calling Range.Copy and naming the destination range. For example, sourceSheet.Range("A1:C10").Copy Destination:=targetSheet.Range("A1") copies cells A1:C10 to…
- VBA Close Workbook Without Saving: Methods and RisksTo close an Excel workbook with VBA and discard its unsaved changes, call the workbook’s Close method with SaveChanges:=False, as in ActiveWorkbook.Close…
- VBA Code to Copy and Paste Values in ExcelTo copy a range’s results without its formulas, use VBA’s PasteSpecial with xlPasteValues; for a fast transfer between same-sized ranges, assign one…
- VBA Check If a Worksheet Is ProtectedIn VBA, check a worksheet’s ProtectContents property: Worksheets("Data").ProtectContents returns True when that worksheet’s cell contents are protected and False otherwise. For a…
- VBA Clear, Copy, and Paste: Excel Macro ExamplesIn Excel VBA, use Range.ClearContents to remove values and formulas while keeping formatting, Range.Clear to clear a range more completely, and Range.Copy…
- VBA Close Workbook Without a Save PromptTo close an Excel workbook without a save prompt, specify what should happen to unsaved changes: use Workbook.Close SaveChanges:=False to discard them,…
- VBA Check If Worksheet Exists: Code and ExamplesTo check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s Worksheets collection and test…
- VBA Check If a Worksheet Is Open in ExcelExcel does not open worksheets independently: a worksheet belongs to a workbook that is open in Excel. To check whether a named…
- VBA Check If Workbook Exists: Paths, Files, and ErrorsYes. In VBA, use Dir$ with the workbook’s full file path and check whether it returns an empty string: an empty result…
- VBA Basic Error Handling: Patterns, Examples, and TipsVBA basic error handling uses On Error to decide what a procedure should do when a runtime error occurs. For most macros,…
- VBA Delete Blank Rows in Excel: Safe MethodsYes. In Excel for Microsoft 365 on Windows, a VBA macro can scan a chosen range from bottom to top and delete…
- VBA Can’t Find Project or Library? How to Fix ItThe VBA error “Can’t find project or library” usually means the workbook’s VBA project has a broken reference to a library that…
- VBA AutoFilter: Filter Text with Like PatternsTo filter rows in Excel VBA for text that is like a pattern, use the AutoFilter method with Excel’s wildcard characters in…
- VBA AutoFilter Method of Range Class Failed?“AutoFilter method of Range class failed” is usually Excel’s run-time error 1004: VBA tried to apply a filter to a range or…
- VBA AutoFilter Mode: How to Check and Control ItIn Excel VBA, Worksheet.AutoFilterMode tells you whether AutoFilter dropdown arrows are displayed on a worksheet; it does not tell you whether any…
- VBA AutoFilter by Color: Filter Fill and Font ColorsYes. In Excel VBA, use the AutoFilter method with Operator:=xlFilterCellColor to filter by a cell’s fill color, or Operator:=xlFilterFontColor to filter by…
- VBA AutoFilter Delete: Remove Visible Rows Safely in ExcelYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data rows, then delete those rows; leave the header outside the range. Check…
- VBA Array: Find a Value and Get Its Index in ExcelTo find a value in a VBA array and get its index, loop from LBound to UBound and return the loop variable…
- VBA Active Worksheet: How to Reference the Current SheetIn Excel VBA, use ActiveSheet to refer to the sheet that is currently active in the Excel window. There is no built-in…
- VBA Active Workbook: Meaning, Use, and ExamplesIn Excel VBA, ActiveWorkbook refers to the workbook that is currently active in the Excel window. Use it when a macro should…
- VBA Add Worksheet After the Last SheetTo add a worksheet after the last sheet tab in Excel, use Worksheets.Add with the last item in the workbook’s Sheets collection…
- VBA Access Find Record: FindFirst, FindRecord, and MoreTo find and move to a matching record in an Access form, use its RecordsetClone with FindFirst, check NoMatch, and copy the…
- Using VBA to Sort Contracts in Excel: A Practical LessonTo sort a contract register by expiration date with VBA, put the records in one rectangular range with headers in row 1,…
- Excel Macro Recorder: How to Record a MacroIn Excel for Microsoft 365 on Windows, go to Developer > Code > Record Macro, give the macro a name, perform the…
- Using MsgBox in VBA: Syntax, Buttons, and Return ValuesIn Excel VBA, use MsgBox to show a pop-up message or ask a user a simple question. Call it with a prompt,…
- Turn On AutoFilter in VBA: Code and ExamplesTo turn on AutoFilter with VBA, select the data range, including its header row, and call its AutoFilter method. For example, Range("A1").CurrentRegion.AutoFilter…
- VBA User Input Box: InputBox, Validation, and ExamplesA VBA user input box is a small dialog that asks someone to enter text, a number, or (with Excel’s Application.InputBox) select…