What This VBA Code Does Have you ever had to search through a large Excel spreadsheet to find specific text? It can be a time-consuming and frustrating task, especially if you're looking for multiple instances of the same word or phrase. Fortunately, with a bit of VBA code, you can automate this process and save yourself a lot of time and effort. One common use case for VBA macros in Excel is to find and highlight specific text within a spreadsheet. For example, imagine you have a spreadsheet containing a list of fruit names, and you need to quickly find and emphasize any cells that contain th ..read more

What This VBA Code Does Sometimes you might be writing macro code that requires reaching out to cloud-based files or applications. In this event, you may want to test that your user has internet connectivity before proceeding with the tasks that require an internet connection. VBA Code: The following VBA function code uses the MSXML2.XMLHTTP object to make a request to Google's homepage. If the status code returned is the value 200, it means that the request to the web address was successful, and therefore, an active internet connection has been established. If there is an error, such as no in ..read more

There may be times when you would like to de-clutter your data labels so that smaller or zero values are removed from your Excel Chart. In this article, you will find a few examples of how you can accomplish this with VBA code. The two methodologies covered are: Utilizing Custom Number Format rules Deleting the Data Label Remove Data Labels Equal To Zero Hide Zeroes With Custom Number Format Rule This VBA code modifies the custom number format rule for the selected chart’s data labels so that zero values are hidden. Sub RemoveDataLabels_ByNumberFormat() 'PURPOSE: Hide Data Labels With V ..read more

What This VBA Code Does If you need to convert Hex color codes into the RGB format, the below macro code will show you how to convert a selected cell range into the proper RGB coding. VBA Code: Sub HexToRGB() 'PURPOSE: Convert Hex Code To RGB Code In Cells 'SOURCE: www.TheSpreadsheetGuru.com/vba Dim R As Integer Dim G As Integer Dim B As Integer Dim cell As Range Dim HexColor As String 'Loop through each cell in selection   For Each cell In Selection.Cells        'Remove # (if applicable)       HexColor = Replace(cell.Valu ..read more

What This VBA Code Does The following VBA code will show you how to utilize the power of Excel’s XLOOKUP function within your macros. VBA Code: The following VBA code provides a variable for each input into your XLOOKUP function. Depending on your particular setup, you may need to tweak how these variables are dimensioned. For example, you might want your Lookup_Value to point to a range instead of entered as a string of text. There is also an error handler included in case your user does not have access to XLOOKUP on the version of Excel they are running. The example VBA code ultimately eithe ..read more

What This VBA Code Does There may be times when you need to incorporate coloring your worksheet tabs in your Excel workbook. This article will cover a handful of situations you may run into while working with VBA code and adding or removing color to your tab name. VBA Code To Change Tab Color If you need to change a specific tab to a certain color, you can reference the sheet name and set it to a specific RGB color code. The below VBA macro code shows you an example of this: Sub ChangeTabColor() 'PURPOSE: Change Tab To Specific Color Worksheets("Sheet2").Tab.Color = RGB(25, 25, 25) End Sub V ..read more

What This VBA Code Does You may find yourself needing to override a current Excel file with changes using VBA code. If you’ve landed on this page, you’re likely experiencing a stoppage in your before the SaveAs command where the user has to manually approve the save action. If you want to prevent this dialog prompt from displaying, you can temporarily turn the alert off using the following VBA code. Prevent Save As Dialog Box Prompt From Showing Below is an example of Excel asking the user if they are sure they wish to override a currently existing file. If you are using VBA code to automate c ..read more

What This VBA Code Does The following VBA examples will show you how to insert a row or column based on a variety of scenarios. VBA Code To Insert Rows The following VBA macro code provides you with a variety of scenarios you may encounter where you need to insert rows. Sub InsertRows() 'PURPOSE: Various Scenarios for Inserting Rows 'SOURCE: www.thespreadsheetguru.com/the-code-vault 'Insert A Row Above The ActiveCell   ActiveCell.EntireRow.Insert 'Insert A Row Above The ActiveCell   ActiveCell.EntireRow.Offset(1).Insert       'Insert A Row Above The A ..read more

What This VBA Code Does This VBA code will show you how to loop through all the PivotTables on the currently selected worksheet and turn off the Autofit setting. This will prevent all your PivotTables from adjusting column widths based on the PivotTable that is currently getting refreshed. VBA Code: Sub TurnAutoFitOff_PivotTables() 'PURPOSE: Turn off Autofit Column Width On Update Setting for every Pivot Table 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault Dim pvt As PivotTable 'Loop through each PivotTable in ActiveSheet   For Each pvt In ActiveSheet.PivotTables   &n ..read more

What This VBA Code Does There may be times when you need to carry out a VBA automation on all PivotTables. In this post, you will see how to perform a loop to iterate through each PivotTable in a given scenario. Loop Through All PivotTables in Workbook Sub LoopPivotTables_ActiveWorkbook() 'PURPOSE: Loop through all PivotTables in the ActiveWorkbook 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault Dim pvt As PivotTable Dim sht As Worksheet 'Loop through each PivotTable in ActiveWorkbook   For Each sht In ActiveWorkbook.Worksheets     For Each pvt In sht.PivotTa ..read more