Excel / VBA

Excel VBA Copy and Rename Files in Folder and Subfolders

This request came from a subscriber who was looking for a macro to rename files in a given folder, and make a copy of the renamed file within the same folder.  Once the files in the top level folder were processed, then cycle through each subfolder and do the same.  The following macro will accomplish … Read more

0 comments

Excel VBA Count Character in Range

This post demonstrates an Excel VBA macro to count the number of instances of a specified character within a range of cells.  The macro prompts the user to input a character, then will loop through each cell and accumulate a count of instances the specified character appears.  The final count is then displayed in a … Read more

0 comments

Excel VBA Alternate Row Colors

This post will demonstrate a simple VBA macro to shade background colors for alternate rows in a specified range.  This can come in handy when you need to reset the alternate colors for different files, or if you are looking to test out different combinations and want to cycle through them quickly. Version of Excel: … Read more

0 comments

Excel VBA Search for Files in Folder and Subfolders

This post details a VBA macro to search for files based on an search string, then lists all files that have a partial match in the file name.  The macro will search a specified folder (user prompted to select base folder), and will then loop through and search each subfolder until all have been examined. … Read more

0 comments

Excel VBA Find Differences Between Two Arrays

The following post will detail how to compare the contents of two arrays (two-dimensional) and highlight cells in the update table where there are differences.  The use case revolves around comparing and updated table against the original to see where cell values have changed.  If a given value has indeed changed, the macro will highlight … Read more

0 comments

Excel VBA Compare Arrays

The following post will compare the contents of one array against the contents of another.  The use case revolves around taking a detail list and comparing each row against a master list, to make sure each row of the detail array exists.  If the detail record does not exist, the cell background color of the … Read more

0 comments

Excel VBA Function Cell Background Color

This post details how to setup a simple function in Excel, using VBA to find the background color of a given cell.  The result will display the text description of the background color.  This allows further utilization in formulas, which was the use case for creating this function. Excel VBA Function Cell Background Color This … Read more

0 comments

Excel VBA Save File as PDF Document

The following post provides an example of how to save an Excel file as a PDF Document, using VBA code.  The macro first prompts you to enter a file name, then opens a second dialog window to pick the destination folder to save the PDF Document.  There is also an option to determine which tabs … Read more

0 comments

Excel VBA Copy and Rename Files

I received a question on post Excel VBA Rename Files and thought a separate post was warranted to cover the answer.  In the following example, I will setup an Excel file to do the following: Allow user to pick a folder and list out the file content (file path and name) Allow user to pick … Read more

0 comments

Excel VBA Split Delimited List

The following post illustrates how to split a delimited list of values in a cell, using a VBA module.  I will demo a simple example where I received a semi-colon delimited list of email addresses, and I wanted to split them up into individual cells.  I use this VBA code frequently, and figured it would be … Read more

0 comments

Excel VBA Custom Function

The following post illustrates how to create a custom function in Excel, using a VBA module.  In particular, I will create two functions that parse a cell containing a full name (First and Last) into two separate cells.  I frequently need to run through this process, so I figured it would be a good way … Read more

0 comments

Excel VBA Find File in Folder and Subfolders

This post demonstrates how to find files in a folder and subfolders that match a user-entered search string.  The user is prompted to select a folder, then enters a search string (file name or partial file name).  The VBA code then searches each folder and subfolder to find any files that match the string.  Finally, … Read more

2 comments

Excel VBA Find String in Range

Excel VBA Find String in Range – This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string.  This is a good technique to employ if a user would like to see where a list of strings appear in a workbook.  The samples will … Read more

0 comments

Excel VBA Rename Files

Earlier this week, I received a request to provide a quick and easy way to rename a list of files in a specific folder.  Each month, the user gets a bunch of PDF files placed in this folder, then needs to manually rename each file for downstream use.  As you can imagine, this is a very … Read more

3 comments

Excel VBA – Create Multiple Named Ranges Based on List

Last week, I encountered a situation where one of my developers needed to populate an Excel Template with 100+ values from a SQL query using a C# program.  There was an existing template in place that receives these values for a report distribution.  Issues were coming up when users began making updates (inserting rows, deleting … Read more

2 comments

Excel VBA – Insert Row at Value Change in Column

In some of my VBA projects, I need to loop through a range of cells and insert a blank row between groups of data, based on a value change in a specific sorted column (Excel dataset is sorted by the respective column).  This gives the user a clear visual look at data groupings, and allows them … Read more

0 comments

Excel VBA – Copy Range into Array

In many of my VBA projects, I need to loop through a range of cells and break up a large dataset into smaller datasets, typically moving the subsets of data to their own worksheets.  Many times, the range of data I need to loop through is the distinct list of items in a particular column.  … Read more

8 comments

Excel VLOOKUP – Approximate Match

Excel’s VLOOKUP function is a very powerful tool that allows users to combine and match two columns in separate datasets.  When performing a VLOOKUP, a given value is matched in a separate range of values within a specific column, returning a value (the desired lookup value) specified in the column index of the row of the match.  The V in … Read more

0 comments

Excel VLOOKUP – EXACT Match

Excel’s VLOOKUP function is a very powerful tool that allows users to combine and match two columns in separate datasets.  When performing a VLOOKUP, a given value is matched in a separate range of values within a specific column, returning a value (the desired lookup value) specified in the column index of the row of the match.  The V in … Read more

0 comments

Excel VBA List Files in Folders and Subfolders

This post details a VBA macro to list all files that exist in a given folder, and will loop through and get the files in each subfolder until all have been examined.  A comment was made on the original post ,  but I decided to create a new post to cover this topic.  The original … Read more

15 comments

Excel VBA – Import SQL Server Data

The following post (Excel VBA – Import SQL Server Data) will demonstrate how to establish a connection to SQL Server, then pull data into your Excel Worksheet.  Further, I will show how to setup a couple buttons for clearing the data in Excel and executing the SQL command to import refreshed data.  This is a simple … Read more

2 comments

Excel VBA – Loop Worksheets

The following macro (Excel VBA Loop Worksheets) will demonstrate how to loop through each worksheet of a given workbook using a couple different types of loops.  Further, I will show how to select a range of cells from a table, and then build new worksheets based on unique values within that range.  In the following examples, I copy … Read more

1 comment

Excel VBA – Loop Through Cells in Range

I was working on a report which gets exported from a Qlikview application.  To add some finishing touches, I needed to evaluate a range of cells, formatting the cell based on the cell value.  In cases where the range of cells was small, this could easily be done manually.  However, when you are dealing with … Read more

0 comments

Excel VBA – Copy Range

One of the most common functions I use in Excel VBA is to select and copy a range of cells.  The following examples will illustrate how to select a range of cells, copy them to another location, and then copy and paste a formula to a desired range. For starters, the following example is based on a … Read more

0 comments

Excel VBA – List Files in Folder

The following macro will loop through the files in specified folder and list them out in your Excel worksheet, based on a designated file type (i.e. anything with file extension of “XLSX”). The actual VBA code is quite simple and can be copied into your VBA Project (modify to suit your needs).  As always, I encourage … Read more

2 comments

Excel VBA – Loop Characters in Cell

The following macro will define a range of cells, then loop through each cell, interrogating each character.  This works well if you are trying to determine how many instances of a given condition exist in each cell. The actual VBA code is quite simple and can be copied into your VBA Project (modify to suit your … Read more

0 comments

Excel VBA – Error Scan

From time to time, I need to work on more advanced Excel projects.  Often, I am tasked with evaluating existing Excel files, many of which utilize extensive formulas.  In many cases errors appear on the spreadsheet and are difficult to detect. Excel VBA – Error Scan I use the following macro to quickly find the actual cell that … Read more

0 comments