Category: VBA

VBA With Power Query: Maximize Efficiency and Automate Your Data Processes in Excel (with VBA code)

Posted on by bonbon

What are the benefits of using VBA with Power Query?

Using VBA in conjunction with Power Query can provide significant benefits, especially when you need to automate complex workflows, integrate data from multiple sources, or perform advanced transformations programmatically. Here are the key benefits of using VBA over just using Power Query directly:

  1. Automation and Repetition:
    • Automation: VBA allows you to automate repetitive tasks, such as importing data from multiple files, applying the same transformations, and saving the results in a consistent format.
    • Scheduling: You can schedule VBA macros to run at specific times, ensuring data is updated automatically.
  2. Customization and Flexibility:
    • Custom Functions: VBA enables you to create custom functions and procedures that can be used within Power Query M code.
    • Dynamic Parameters: You can pass dynamic parameters to Power Query queries using VBA, allowing for more flexible data processing. This alone is a huge benefit!!
  3. Integration with Other Applications:
    • Interoperability: VBA can interact with other applications and services, such as databases, web APIs, and email clients, enhancing the capabilities of Power Query.
    • Data Export: You can use VBA to export data to various formats, such as PDF, CSV, or other Excel files, after it has been processed by Power Query.
  4. Complex Logic and Control:
    • Conditional Logic: VBA provides powerful conditional logic and control structures that can be used to handle complex data processing tasks.
    • Error Handling: You can implement robust error handling in VBA to manage unexpected issues during data processing.
  5. User Interface:
    • Custom UserForms: VBA allows you to create custom user interfaces (UserForms) for data entry and interaction, making it easier for users to perform complex tasks without needing to know Power Query M code.
    • Buttons and Macros: You can add buttons and macros to Excel worksheets to trigger VBA scripts, making it user-friendly.
  6. Advanced Data Manipulation:
    • Data Cleaning: VBA can be used for advanced data cleaning tasks, such as removing specific patterns, handling missing data, and normalizing data formats.
    • Data Transformation: VBA can perform complex transformations that might be difficult or impossible to achieve with Power Query alone.
  7. Version Control and Collaboration:
    • Version Control: VBA code can be version-controlled using tools like Git, allowing for better collaboration and tracking changes.
    • Shared Macros: You can share VBA macros with your team, ensuring consistency in data processing workflows. This can be especially helpful for vacation coverage or spreading the workload among multiple team members!
  8. Performance Optimization:
    • Efficiency: For large datasets, VBA can be more efficient in certain scenarios, especially when combined with Power Query for initial data loading and filtering.
    • Resource Management: VBA can manage system resources more effectively, ensuring smooth performance during data processing.

When to Use VBA Over Power Query

  • Complex Workflows: When you need to perform a series of complex transformations and data manipulations that are difficult to achieve with Power Query alone.
  • Integration with Other Systems: When you need to integrate Excel with other applications, databases, or web services.
  • Automated Reporting: When you need to automate the generation of reports and dashboards based on dynamic data sources.
  • Custom User Interfaces: When you need to create custom user interfaces for data entry and interaction.
  • Advanced Error Handling: When you need robust error handling and logging for data processing tasks.

When to Use Power Query Alone

  • Simple Data Transformation: When you need to perform simple data transformations and cleaning tasks.
  • Data Visualization: When you need to create dynamic data visualizations and dashboards.
  • Data Integration: When you need to integrate and combine data from multiple sources without complex logic.
  • Data Refresh: When you need to refresh data regularly from external sources.
  • Data Transformation: Power Query allows for complex data transformations, such as filtering, merging, and aggregating data.
  • Refreshable Data: Data imported using Power Query can be easily refreshed to update with new data.
  • Scalability: Power Query is better suited for larger datasets and more complex data processing tasks.

VBA with Power Query Code Samples with Explanations

Use Case: Importing Data from CSV Files Using Power Query

Power Query is a more advanced and flexible tool for data import and transformation in Excel. It allows for more complex data transformations and can handle larger datasets more efficiently. Here’s how you can use VBA to import a CSV file using VBA with Power Query.

End Sub

Explanation of Power Query VBA Code

  1. File Path and Connection Name:
    • filePath is the path to your CSV file
    • connName is the name of the Power Query connection.
  2. Delete Existing Connection:
    • The code checks if the connection already exists and deletes it to avoid conflicts.
  3. Create New Power Query Connection:
    • The Queries.Add method creates a new Power Query connection.
    • The Formula parameter specifies the Power Query M code to import and transform the CSV file.
  4. Load Query into Worksheet:
    • A new worksheet is created, and the query is loaded into it using LoadFromText

Use Case: Importing and Transforming Data from an Excel File Using Power Query and VBA

Objective: Import data from a specific worksheet in an Excel file, filter out rows with specific criteria, perform some transformations, and load the cleaned data into a new worksheet.

Preparation:

  1. Prepare the Source Excel File:
    • Ensure your source Excel file is located at a known path, e.g., C:\Data\source_data.xlsx.
    • Ensure the data is in a worksheet named SalesData.
  2. VBA Code to Import and Transform Data Using Power Query:

Explanation of the VBA Code

  1. Set File Path, Workbook Name, Sheet Name, and Names:
    • sourceFilePath is the path to your source Excel file.
    • sourceWorkbookName is the name of the source Excel file.
    • sourceSheetName is the name of the worksheet containing the data.
    • connName is the name of the initial Power Query connection.
    • queryName is the name of the transformed Power Query query.
  2. Delete Existing Connection and Query:
    • The code checks if the connection and query already exist and deletes them to avoid conflicts.
  3. Create a New Power Query Connection:
    • The Queries.Add method creates a new Power Query connection to import data from the specified worksheet in the Excel file.
    • The Formula parameter specifies the Power Query M code to import the data.
  4. Create a New Power Query Query for Transformation:
    • This query uses the initial connection as its source.
    • It skips the first row (assuming headers).
    • It removes duplicates based on specified columns.
    • It filters rows where Sales is greater than 100.
    • It sorts the filtered rows by Date in ascending order.
    • It adds a new column TotalSales calculated as Sales * Quantity.
  5. Load the Transformed Query into a New Worksheet:
    • A new worksheet is created, and the transformed query is loaded into it using LoadFromText.
  6. Format the Worksheet:
    • The code automatically fits the columns and applies a table style for better readability.

Detailed Steps

  1. Prepare the Source Excel File:
    • Update the path to match your source Excel file.
    • Ensure the data is in a worksheet named SalesData, or update that variable to match your data.
  2. Open VBA Editor:
    • Press Alt + F11 to open the VBA editor.
  3. Insert a New Module:
    • In the VBA editor, go to Insert > Module to create a new module.
  4. Copy and Paste the VBA Code:
    • Copy the above VBA code and paste it into the module.
  5. Run the Macro:
    • Close the VBA editor and return to Excel.
    • Press Alt + F8, select ImportAndTransformExcelDataWithPowerQuery, and click Run.
  6. View the Results:
    • A new worksheet named TransformedData will be created, showing the imported and transformed data.

Additional Tips

  • Customizing Columns:
    • You can customize the column names and the criteria for filtering and sorting based on your specific dataset.
  • Handling Different Workbooks and Sheets:
    • Adjust the sourceFilePath, sourceWorkbookName, and sourceSheetName variables to match your source file and worksheet.
  • Error Handling:
    • Add error handling to manage potential issues, such as file not found or invalid data.

Example: Combining VBA with Power Query for Automation of Reporting

Here’s a more detailed example that combines VBA with Power Query to automate a complex data processing workflow that can automate data reporting from start to finish, including transforming the data and outputting a user-friendly report.

Use Case: Automating Data Import, Transformation, and Reporting

Objective: Import data from multiple Excel files, perform transformations, and generate a consolidated report.

VBA Code:

Explanation of the VBA Code

  1. Set Folder Path and Names:
    • folderPath is the path to the folder containing the Excel files.
    • connName is the name of the initial Power Query connection.
    • queryName is the name of the transformed Power Query query.
  2. Delete Existing Connection and Query:
    • The code checks if the connection and query already exist and deletes them to avoid conflicts.
  3. Initialize a New Worksheet:
    • A new worksheet named ConsolidatedData is created to store the consolidated data.
  4. Loop Through Excel Files:
    • The code loops through all Excel files in the specified folder.
    • For each file, it creates a new Power Query connection to import data from the SalesData worksheet.
  5. Create a New Power Query Query for Transformation:
    • This query uses the initial connection as its source.
    • It skips the first row (assuming headers).
    • It removes duplicates based on specified columns.
    • It filters rows where Sales is greater than 100.
    • It sorts the filtered rows by Date in ascending order.
    • It adds a new column TotalSales calculated as Sales * Quantity.
  6. Load the Transformed Query into the Consolidated Worksheet:
    • The transformed data is loaded into the ConsolidatedData worksheet.
  7. Delete the Power Query Connection and Query:
    • After processing each file, the connection and query are deleted to clean up.
  8. Format the Worksheet:
    • The code automatically fits the columns and applies a table style for better readability.

Conclusion

Using VBA in conjunction with Power Query provides a powerful combination for automating and managing complex data workflows. While Power Query is excellent for data transformation and integration, VBA offers the flexibility and control needed for advanced automation and integration tasks. By combining these tools, you can create robust and efficient data processing solutions.

Feel free to comment other examples you would like to see as we continue to explore automation of workflows via both VBA and Power Query!

code execution has been interrupted window

How to Make Excel Wait or Pause in a VBA Macro

Posted on by Bb

When running macros, I have often found the need for an Excel wait or pause, mainly so that other things can happen. DoEvents surely helps for waiting for things to happen within Excel, but sometimes you need to wait for processes to start or finish outside of Excel before moving onto other steps in VBA.

Why Wait?

I am a big fan of automating processes, especially with VBA in Excel. For example, I update and save a spreadsheet to SharePoint that a Power BI model is using. I have a Power Automate process set up that automatically refreshes the Power BI model whenever this particular spreadsheet is updated. The challenge is that Power BI temporary locks the workbook as it reads it, so if I am updating a few times in a short amount of time, Excel needs to wait until the SharePoint file is unlocked.

Also, I have another process where I call a Python script and once that is done, I continue on with VBA code to process the workbook Python outputs. I need Excel to wait while the Python code runs. Side note: let me know if you would like me to write more about these other automated processes.

The Solution

Good news – making Excel wait is very easy! Here is the simple code:

This will have Excel wait for 50 seconds since the time is represented as (H:MM:SS). You can adjust as needed.

Bonus

In my code, I don’t need it to wait every time – only when it runs into an issue saving the workbook on SharePoint. Here is how I have set my code using an error handler in my VBA macro.

I hope this helps you! Please comment or reach out if you would like to see other topics covered. I truly enjoy writing about Excel tips and tricks, especially VBA and automation!

Addressed in this post (to help others find this):

  • How to pause Excel
  • How to make Excel pause
  • Make VBA pause
  • Make VBA wait
  • Make Excel wait
  • VBA code wait, VBA macro wait
  • VBA code pause, VBA macro pause
  • VBA wait for another application

Solution for Power BI Refresh Error in the Web Service

Posted on by Bb

Web Service Power BI Refresh Error When Trying to Refresh On Premises 32-bit Database/Source

I recently worked with a client who is still using an old 32-bit Oracle instance with lots of dependencies. Upgrading is not currently an option. In her Power BI report/model, she is querying several tables in the Oracle database. Unfortunately due to the 32-bit architecture she was not able to schedule her refreshes, even using the desktop gateway.

The Power BI refresh error she received was “Failed to update data source credentials: An error happened while reading data from the provider: ‘Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.'”

Workaround for Power BI Refresh Error in Web Service When Trying to Refresh On Premises 32-bit
Power BI Architecture Error

As a result, she found she could only refresh this report using the Power BI desktop client. She would then manually publish to the cloud. This was cumbersome as the data refresh took quite some time, and she needed it done daily. I came up with two solutions for her, both of which we implemented and tried. One proved to better for us, and ultimately she is successfully using this first solution/workaround that I will cover.

Solution #1 – Export Query Results to CSV and Post on SharePoint

  • So just as the title of the section states, the final workaround was for us to automate the generation of CSV files to ultimately be consumed by Power Query.
  • We did this by using a combination of scripts pulling data via SQL in VBA (Access, Excel) and via SQL in Python. You can pull the data any way you are most comfortable (macros, etc.).
  • The query results are then outputted to the CSV files, which are written directly to SharePoint folders.
  • After all this was set up and the initial files were changed, we then changed the source of the data in Power Query, to now navigate to SharePoint folders and then process the CSV files.
  • It is important to note that you are not required to use CSV files. These made sense in our case due to the amount of data we were working with (generating several very large files nightly). You can write to any supported file format if that works better in your case, as long as you can place it on SharePoint and Power Query can process it.
  • Also as noted we used a separate folder for each unique query. If you are also replacing several queries, you can either do this (write to separate folders) or if you would prefer just one, then utilize different naming convention patterns to filter.
  • Everything is then kicked off via a command line batch script (.bat) using the Windows task scheduler on her desktop to run overnight. We also set up the same script on a colleague’s PC as a backup/for redundancy in case something were to happen, but it is not actively running.
  • After refreshing and publishing the updated report with the new SharePoint sources, I then went into settings in the Web Service and added the credentials there for SharePoint. I saved and kicked off the refresh in the browser.
  • The refresh was successful, so I then set a daily schedule for the web service to refresh automatically. No intervention has been needed since implementation!

Solution #2 – Automate the Power BI Desktop Refresh Process Using Python

  • This method is much less convenient in my opinion, as after it had been implemented, it failed often and required manual intervention several times before we moved on to the other solution.
  • Another caveat is that this solution requires the user to have at least some familiarity with Python (or finding someone with Python knowledge to tap as a support resource).
  • Also, obviously someone needs to install Python, the required package and then set up the automation to run the scripts.
  • Important note – it is possible to install a portable version of Python if you do not have admin rights on the desktop.
  • The package is called pbixrefresher (repo is linked).
  • This gentleman does a great job of walking through setting it up in his YouTube video about it: https://youtu.be/wtXXpicCUlY

I hope this post has been helpful and has maybe given you some ideas on how to proceed if you or an organization you are supporting is facing this same situation with older data sources. Also, I’d love to hear about other solutions if you’ve faced the same issue and handled it differently.

Please reach out with any questions or if you would like to see further coverage of any of the concepts/steps mentioned in this post. I am also available on a limited basis for consulting as my schedule is full as of late (a good thing!). Just fill out the contact form and I will get back to you.

Executing SQL in Excel Tool/Template

Amazing Method for Executing SQL Queries in Excel Using VBA (Import Data) – No Need to Use Multiple Applications!

Posted on by Bb

I have found several use cases for my team and I to save time and utilize Excel alone for pulling data via SQL, as opposed to running queries in Access, SSRS, SSMS or Toad and then exporting that data into Excel for manipulation and analysis. There is not only the benefit of saving time by skipping the export/import process, but also in the ability to build templates/files and save them for quick and easy future SQL pulls. The days of needing multiple applications for your SQL pulls to import into Excel are over!

The best use cases I’ve found that support this method are repeatable processes where the same input variables are required each time, and where the query results will not exceed the row limitations of an Excel sheet (~1M). Even in this case, you may find the first two sections of this post useful for learning about constructing and executing SQL queries for use outside of Excel. This alone may save you time if you are in the habit of writing long and involved queries with changing criteria.

If you are exceeding the Excel row limit but still want to work in Excel, using Power Pivot can handle this, so you may wish to utilize Power Query instead. See my post on utilizing parameters with Power Query here for more information on how to import the data this way.

I have also found this approach to be useful when pulling data from different sources using the same criteria. It saves me lots of time.

In this example, we will construct and then run a simple SQL query using a few parameters/variables that we enter into the spreadsheet.

Follow along so you can see how it works in practice, and then I encourage you to try it out with your own data. Once you master this method, hopefully you will find amazing ways to apply it to your own work!

A copy of the file described in this post is available for purchase – just reach out to me for information. A more complex version that handles wildcards is also for sale.

Setting Up the Variables/Criteria/Parameters for Executing SQL Queries in Excel

My goal will be to run this query: Select * from Store where CreatedDate > #3/1/2022# and State = ‘GA’

  • The two variables in this example will be the created date (3/1/2022) and the State (GA).
  • At the top of my first sheet, we will designate named ranges for the two inputs.
  • Simply type in Created Date in cell A1 to identify the input, then in cell B1, type in 3/1/22.
  • Make B1 a named range – we will call this CreatedDate.
  • Similarly, in A2, type in State, and then in B2, type in GA. Ultimately, you can add in data validation and use lists, but let’s keep it very simple for now.
  • Make cell B2 a named range called State.
    • If you are not familiar with named ranges, the easiest way to make one is to select the cell or cells and then type the name into the dropdown box at the top left next to the formula bar, where the cell address is displayed. Alternately, you can press CTRL+F3 to bring up the Named Ranges dialog box.
SQL Execution in Excel VBA - setting up the inputs for the variables using Named Ranges
Executing SQL Queries in Excel – Setting up Inputs for Variables Using Named Ranges

Constructing the SQL Statement

From here, we can either construct the SQL statement in an Excel sheet or in VBA. I have found that it is far easier to construct in an Excel sheet, especially for complex statements with many criteria. This way certainly allows for more flexibility and better troubleshooting in my opinion. Also, it’s easier to manage from a support standpoint.

  • I will add a new sheet/tab to house the SQL Statement. I will start by typing in the full statement I expect in to A1, just to use as a reference as I build out the statement in the cells below.
  • I then break the statement out line by line, using column A for the variables, and column B for the statements and column C for the joining of statements with variables.
  • Finally, I use the TEXTJOIN function in cell A4 to join together all of the rows in the combined section. I used a space as the delimiter (” “). This ensures proper spacing throughout the resulting SQL statement.
  • I then make cell A4 a named range called SQLStatement.
  • See image below to see how I have achieved all of this.
    • I have included some helper/formula notes on how to format the variables, since the SQL statement has to be properly formatted in order to work.
Executing SQL Queries in Excel variable setup
Construction of SQL Queries in Excel

VBA Code for Executing SQL Statement and Importing the Data

  • Open the VBA editor (Alt + F11).
  • Add a new module.
  • Add a new subroutine. I’ve called mine SQLPull.
  • Very important step – enable the required references. Go to the Tools menu and select References. Select the ActiveX Data Objects 2.8 Library and Recordset 6.0 Library – see the last two checked in the image below (yours may be different versions). Click OK to save.
VBA Project References – ActiveX Data Objects/Recordset

Here is an overview the VBA code I wrote, that is pasted below.

  • Note that it will connect to the database you identify after you update the connection string if needed.
  • It will then execute the SQL select statement and grab the recordset.
  • The code will then write the headers from the query into the cells identified.
  • Next, it will paste the rows that are returned by the SQL query, starting in the specified cell.
  • Finally it will close the connection and end the subroutine.
  • For my applications of this, I like to switch to manual calculation and turn off screen updating because I have found it improves the speed of loading the data. You may choose to leave these alone if working with less data.

I have added commentary and explanations throughout to hopefully help you to modify as needed to support your own needs.

Sub SQLPull()

‘* www.bonbonsguide.com *
‘Importing Data into Excel using a SQL select statement in a cell

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath, sconnect, sqlstring As String
Dim icols As Long

””””””””””””””””””””””””’
‘Database Name/Path
””””””””””””””””””””””””’
DBPath = STOREDB

‘NOTE:
‘If you are querying a SQL Server or Oracle DB, use the name of the database:
‘DBPath = YourDatabaseName

‘If you are querying an Access Database, use the path to the file in quotes:
‘DBPath = “\myfileserver\Bonbon\MyAccessDB.accdb” OR “F:\Bonbon\MyAccessDB.accdb”

””””””””””””””””””””””””’
‘Connection String
””””””””””””””””””””””””’
‘The DSN is the existing ODBC connection on your PC – this must be set up first!

‘Uncomment the applicable sconnect string for your database and modify as needed. For MS Access, no modifications should be needed.

‘SQL Server using Windows Authentication:
sconnect = “Provider=MSDASQL.1;DSN=STOREDB;DBQ=” & DBPath & “;HDR=Yes’;”

‘ORACLE Connection using an UID/PWD:
‘sconnect = “Provider=MSDASQL.1;DSN=WAREHOUSE;uid=bonbonsguide;pwd=helpsus;DBQ=” & DBPath & “;HDR=Yes’;”

‘MS Access:
‘sconnect = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & DBPath & “;”

””””””””””””””””””””””””””””””””””””””””””””””””””””’
‘Set Timeouts (These may not be required in your environment)
””””””””””””””””””””””””””””””””””””””””””””””””””””’
Conn.ConnectionTimeout = 200
Conn.CommandTimeout = 200

””””””””””””””””””””””””’
‘Connect to datasource
””””””””””””””””””””””””’
Conn.Open sconnect

””””””””””””””””””””””””’””””””””””””””””””””””””’
‘VBA get SQL Statement from Sheet/Named Range
””””””””””””””””””””””””’””””””””””””””””””””””””’
sqlstring = Range(“SQLstatement”)

””””””””””””””””””””””””””””””””””
‘Get the recordset – this command will execute the SQL Statement’
””””””””””””””””””””””””””””””””””
mrs.Open sqlstring, Conn

””””””””””””””””””””””””””””””””””
‘Return the Header Names
‘”””””””””””””””””””””””””””””””””’
‘If you don’t need the headers or are using your own, comment the block out

‘Where Headers will be pasted:
‘Sheet1 = Sheet identifier – use Sheets(“name of sheet”) to use sheet name instead
‘Cells (4 – indicates row 4, + 3 indicates to start in column C) … Edit this as needed

For icols = 0 To mrs.Fields.Count – 1
Sheet1.Cells(4, icols + 3).Value = mrs.Fields(icols).Name
Next

””””””””””””””””””””””””””””””””””
‘OPTIONAL – SPEED UP IMPORT
””””””””””””””””””””””””””””””””””
‘If retrieving lots of records, it may speed it up if you set calculation to manual during the import process.
‘Setting the screen updating to false may also speed up the import. Comment these out if preferred.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

””””””””””””””””””””””””’””””””””””””””””””””””””’””””””””””””””””””””””””’
‘OPTIONAL – CLEAR DATA IN ROWS BEFORE PASTING RECORDSET OR DO OTHER PREP
””””””””””””””””””””””””’””””””””””””””””””””””””’””””””””””””””””””””””””’
‘Add your code here.

””””””””””””””””””””””””””””””””””
‘Paste the Rows/Records
‘”””””””””””””””””””””””””””””””””’
‘Importing rows returned – the range below will be where the data starts – line this up with the headers, one row below.
Sheet1.Range(“C5”).CopyFromRecordset mrs

””””””””””””””””””””””””””””””””””
‘Close the Recordset
””””””””””””””””””””””””””””””””””
mrs.Close

””””””””””””””””””””””””””””””””””
‘Close Connection
””””””””””””””””””””””””””””””””””
Conn.Close

”””””””””””””””””””””””””””””””””””””””””””’
‘Turn automatic calculation and screen updating back on.
”””””””””””””””””””””””””””””””””””””””””””’
Application.Calculation = xlCalculationAutomatic
Application.Calculate
Application.ScreenUpdating = True

End Sub

Wrapping Up

I hope this helps you out. Below is the final look of my file for this example. I have added a button for the users to click in order to run the VBA code and execute the SQL by assigning the macro. I have formatted so the data returns in a table for easy manipulation and analysis. I have also updated the general formatting and named the sheets.

To save you time, a copy of the file is available for purchase – just reach out to me for information.

I also have another sample template file available that allows you to put multiple criteria in a list and constructs the query accordingly, searching all items. It even allows for the use of wildcards by automatically formulating LIKE statements! Exciting stuff! Contact me if interested in purchasing.

Tool for Execution of SQL Queries in Excel (For Sale)
Tool for Execution of SQL Queries in Excel (For Sale)

Delete Every Other Row or Column in Excel (using Formula or VBA)

Posted on by Bb

Today we are going to explore various ways to delete every other row or delete every other column in Excel. VBA macro code is included to help you on your way if that is the path you choose.

Additionally, we will also cover how to delete every third/fourth/fifth row or column in Excel.

To delete alternate rows, you can use a helper column and use a formula that helps you identify alternate rows. This could be done by using the ISEVEN function that checks each row number and returns TRUE if the row is even and false if it isn’t.

Once you have this, you can easily filter the rows with FALSE in the helper columns and delete these.

In case you want to delete every third row, you need to use the MOD function to identify every third row. Once you have it, you can easily filter and delete every third row or every fourth row.

I also cover a method to use a simple VBA code to delete alternate rows in Excel.

Below is that VBA CODE TO DELETE ALTERNATE ROWS:

When it comes to deleting alternate columns, you cannot filter these. You can instead sort and bring all those columns together that you want to delete.

I cover a simple method that uses the MOD function to identify alternate columns and then sort these from left-to-right to bring these together. Once you have these in one place, you can select and delete these.

And there is also a VBA code that you can use to delete alternate columns.

Below is the VBA CODE TO DELETE ALTERNATE COLUMNS

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube