Arrays are used to store sets of data. A range with data in Excel can be stored in a two-dimensional (2D) array. A column or row are also Range objects and have by default 2 dimensions (one of which has only one element – either the column or the row). In this post we see how to put data from a range into a 2D array, and data from a single column or row into a 1D array.
In a previous post we saw how to apply conditional formatting rules in Excel with VBA macros. In that example, formatting was applied to a column based on unique cell values, thus highlighting each unique cell value with the same color. In this post we see how to apply conditional formatting to a column based on the value in another column. We can tweak the code slightly to highlight the entire row as well.
Microsoft Excel allows to sort a range based on cell values, cell and font color, and conditional formatting, in ascending or descending order, or using a custom list. In this post we see how to create a custom list of priority categories to sort a range with data accordingly. Excel includes built-in custom lists for weekdays and months. This macro example adds another custom list that is used temporarily to sort the range values. After sorting the table, the custom list is deleted.
In this post we see how to add consecutive numbers to cells in the Excel worksheet using VBA macros. We review two simple options: 1. Using an classic programming For loop, and 2. Using the AutoFill function (a method of the Range object). Using AutoFill requires to add the first 2 numbers to set the sequence of the following numbers (option 2a), or adding just one number and choosing to auto fill with a series (option 2b).
There are several ways to clear cells based on cell value with Excel VBA. The easiest, and probably also most efficient way to do it is using the Replace method of the Range object, which can replace the target value with an empty string in a given range or entire worksheet. Another possibility is looping through all cells in a range and setting a condition to clear the contents for a target value. This gets slower the bigger the range. On the other hand, this method allows not only to clear values, but also to change formatting or delete the target cell, for example. It also allows to set various conditions, instead of a single value.
Conditional formatting is a feature of Microsoft
Excel that allows highlighting certain values to easily identify those cells.
Excel offers various options for numerical values (greater or less than,
between, top 10, data bars, color scales, icons, etc.), but there are less
options for text. In both cases, we can set rules manually to apply formatting
based on a given condition. This post shows how to apply rules automatically
using Excel VBA macros. The macro specifically applies formatting to a column
based on unique cell values. As a result, each unique cell value is highlighted in the same color.
In this post we see how to delete rows based on given criteria in Excel using VBA macros. The macro loops through each row with content in the worksheet, and checks if certain criteria for one or more fields is met to delete the row. The criteria can be any value along with the relational operators of the If statement. For example, that could be when the value is below a certain price, or when the profit is above a certain margin. It can also check for specific text, a string of certain length, etc. In the below example, we delete rows for products with a volume below 700 ml or price above 1000 (of the given currency) in the dataset.
In this post we see how to consolidate filtered data from multiple sheets into separate sheets for each unique entry using Excel VBA macros. The macro loops through each worksheet and filters each unique entry in column A, then adds a new sheet for each unique entry to copy/paste the data. It is a modification of a previous macro (Copy Filtered Data To New Sheets), that allows to combine data from various sheets.
In this post we see how to copy each column with data to a separate worksheet in Excel using VBA macros. This is useful when dealing with many columns that need to be moved to other sheets to have a separate record for the data in each column or maybe do some further analysis or report. The macro loops through each column in the reference worksheet and creates a new sheet with the name of the column header, while copying the rest of the data to the first column in that new sheet. The macro also sets a different tab color for each new sheet.
In this post we learn to stack or combine multiple columns into a single column in Excel using VBA macros. Depending on the scenario, that’s something you can probably do without macros, but in some cases, and especially when dealing with a lot of data, is more convenient, faster, and more secure to do it with macros. This is a simple macro to stack the selected columns into one column either in the same or a different worksheet. The macro can easily be adapted to stack the columns skipping the headers by changing just one line of code (see more in the explanation below).
In this post we see how to strikethrough the text in a cell in Excel using VBA macros. The macro example consists of an event procedure triggered when trying to delete the content of a cell. It actually prevents deleting the value of a cell and strikes through the content instead. This somehow protects the content in a worksheet from being deleted, without using worksheet protection, and may be useful when sharing a worksheet with other users.
WARNING: The code below prevents from selecting multiple cells and deleting the value of cells in the sheet where the VBA code is added.
In this post we see how to group cells or rows with the same format in Excel using VBA macros. Grouping cells or rows in Microsoft Excel is quite helpful when dealing with lots of data arranged in categories, phases, etc., which we may need to monitor or keep updating regularly. Then, we can collapse groups when complete or when not relevant at a given time, thus making space and setting focus on the target data. The Excel VBA macro loops through the dataset, and groups rows based on the format, specifically, the example below groups rows sharing the same category or headers with bold text. That can be changed to any other format condition such as font size, color, etc.
In a previous post we saw how to Compare Two Worksheets in Excel using VBA macros. In this post we see how to compare two columns with data. Each column is treated as a list of items, and the goal is to compare which items or values are not in common. Thus, the macro compares the two lists or columns in the active sheet, and finds the discrepancies between the two. In this macro example, the discrepancies are highlighted in yellow and also added to another sheet. The lists are supposed to have unique entries, but the macro also works is entries are repeated. In that case though, all of them will be highlighted and also added to the other sheet, thus having repeated terms there. The macro can be easily tweaked to avoid that and show only unique entries if used to compare two columns or lists with repeated entries.
In this post we see how to sort and merge a group of cells with the same value in Excel using VBA macros. Selected values are sorted in ascending order, as per the value in column A first, and then column B. Then, the groups of contiguous cells with the same value are merged.
In a previous post we have seen how to autofill table gaps in Excel using VBA macros. In this post we see another way to do it with less code. This macro example uses the CurrentRegion property to select the table, the SpecialCells property to target the gaps only, and the Offset property to get the value to be filled.
In this post we see how to split text into columns in Excel using VBA macros. The macro splits the text in one column into as many columns as dictated by the text separator (a comma, space, etc). In this example, we use the TextToColumns method of the Range object. But this can also be achieved using the Split VBA function to create an array and then write into columns.
Sometimes we need to manually create a table with many repeating terms and is time-consuming to use Excel’s AutoFill/FillDown or Go To Special features to fill the gaps. We can easily do that with Excel VBA. This macro example fills table gaps with values above within each column in the selected range.
In a previous post, we have seen how to get a list of unique entries in Excel using VBA macros. Now we are going to add a validation list with available unique entries to the selected range using Excel VBA.
