Chapter 7
In This Chapter
Converting values in a range
Trimming and truncating text
Replacing blanks cells with values
Adding text to existing values
Handling duplicates in a range
Working with AutoFilter drop-downs
When working with information in Excel, you often have to transform the data, cleaning, standardizing, or shaping it in ways that are appropriate for your work. Transforming data can mean anything from cleaning out extra spaces to padding numbers with zeros to filtering data for certain criteria.
This chapter shows you some of the more useful macros you can use to dynamically transform the data in your workbooks. If you like, you can combine these macros into one, running each piece of code in a sequence that essentially automates the scrubbing and shaping of your data.
One of the basic data manipulation skills you’ll need to learn is copying and pasting a range of data. Doing this manually is fairly easy. Luckily, it’s just as easy to copy and paste by using VBA.
In this macro, you use the Copy method of the Range object to copy data from D6:D17 and paste to L6:L17. Note the use of the Destination argument, which tells Excel where to paste the data:
Sub Macro1()
Sheets("Sheet1").Range("D6:D17").Copy _
Destination:=Sheets("Sheet1").Range("L6:L17")
End Sub
When working with your spreadsheet, you likely often have to copy formulas and paste them as values. To do this in a macro, you can use the PasteSpecial method. In this example, you copy the formulas F6:F17 to M6:M17. Note that you're not only pasting as values by using xlPasteValues but also applying the formatting from the copied range by using xlPasteFormats.
Sub Macro1()
Sheets("Sheet1").Range("F6:F17").Copy
Sheets("Sheet1").Range("M6:M17").PasteSpecial xlPasteValues
Sheets("Sheet1").Range("M6:M17").PasteSpecial xlPasteFormats
End Sub
To implement this macro, you can copy and paste it into a standard module:
Sometimes, you may want to apply formulas in a certain workbook, but you don’t necessarily want to keep or distribute the formulas with your workbook. In these situations, you may want to convert all the formulas in a given range to values.
In this macro, you essentially use two Range object variables. One of the variables captures the scope of data you are working with, whereas the other is used to hold each individual cell as you go through the range. Then you use the For Each statement to activate or bring each cell in the target range into focus. Every time a cell is activated, you check to see whether the cell contains a formula. If it does, you replace the formula with the value shown in the cell.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: If cell has formula, set to the value shown
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. MyRange holds the entire target range, and MyCell holds each cell in the range as you enumerate through them one by one.
When you run a macro, it destroys the undo stack, so you can’t undo the changes a macro makes. Because you're changing data, you need the option of saving the workbook before running the macro. Step 2 performs this task. You call up a message box that asks if you want to save the workbook first. You have three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range that was selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell as it goes through.
After a cell is activated, the macro uses the HasFormula property in Step 5 to check whether the cell contains a formula. If it does, you set the cell to equal the value shown in the cell. This effectively replaces the formula with a hard-coded value.
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
When you import data from other sources, you may wind up with cells where the number values are formatted as text. You typically recognize this problem because no matter what you do, you can’t format the numbers in these cells to numeric, currency, or percentage formats. You may also see a smart tag on the cells that tells you the cell is formatted as text, as shown in Figure 7-1.
It’s easy enough to fix this manually by clicking the Text to Columns command on the Data tab (see Figure 7-2). The Text to Columns Wizard dialog box appears, as shown in Figure 7-3. You don't need to go through all the steps in this wizard; simply click the Finish button to apply the fix.
Although the Text to Columns fix is simple, Excel doesn't let you perform this action on multiple columns. You have to apply the fix one column at a time, which is a nuisance when you have this issue in many columns.
This section provides a simple macro that can help save your sanity.
In this macro, you use two Range object variables to go through your target range, leveraging the For Each statement to activate each cell in the target range. Every time a cell is activated, you simply reset the value of the cell. This macro in effect does the same thing as the Text to Columns command.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Reset the cell value
If Not IsEmpty(MyCell) Then
MyCell.Value = MyCell.Value
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. The MyRange variable holds the entire target range, and the MyCell variable holds each cell in the range as the macro enumerates through them one by one.
When you run a macro, it destroys the undo stack, so you can’t undo the changes a macro makes. Because the macro is changing data, you need the option of saving the workbook before running the macro. Step 2 performs this task. Here, you display a message box that asks if you want to save the workbook first. You have three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell as you go through.
After a cell is activated, the macro uses the IsEmpty function to make sure the cell is not empty. You do this to improve performance a little by skipping the cell if it's empty. You then simply reset the cell to its own value. This step removes any formatting mismatch.
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
Legacy and mainframe systems are notorious for outputting trailing minus signs. In other words, instead of a number like -142, some systems output 142-. This obviously wreaks havoc on your spreadsheet — especially if you need to perform mathematic operations on the data. The nifty macro in this section goes through a target range and fixes all negative minus signs so that they show up in front of the number instead of at the end.
In this macro, you use two Range object variables to go through your target range, leveraging the For Each statement to activate each cell in the target range. Every time a cell is activated, you convert the value of the cell into a Double numeric data type by using the CDbl function. The Double data type forces any negative signs to appear at the front of the number.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Convert the value to a Double
If IsNumeric(MyCell) Then
MyCell = CDbl(MyCell)
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. The MyRange variable holds the entire target range, and the MyCell variable holds each cell in the range as you enumerate through them one by one.
When you run a macro, it destroys the undo stack, so you can’t undo the changes a macro makes. Because you're changing data, you need the option of saving the workbook before running the macro. Step 2 performs this task by displaying a message box that asks if you want to save the workbook first. You have three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name — Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell as it goes through.
After a cell is activated, Step 5 uses the IsNumeric function to check to see whether the value can be evaluated as a number. This step ensures that you don’t affect textual fields. You then pass the cell’s value through the CDbl function, which converts the value to the Double numeric data type, forcing the minus sign to the front.
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
A frequent problem when you import dates from other sources is leading or trailing spaces. That is, the imported values have spaces at the beginning or end of the cell. These extra spaces make it difficult to do things like VLOOKUP or sorting. Here is a macro that makes it easy to search for and remove extra spaces in your cells.
In this macro, you enumerate through a target range, passing each cell in that range through the Trim function:
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Trim spaces
If Not IsEmpty(MyCell) Then
MyCell = Trim(MyCell)
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. The MyRange variable holds the entire target range, and the MyCell variable holds each cell in the range as the macro enumerates through them one by one.
When you run a macro, it destroys the undo stack, so you can’t undo the changes a macro makes. Because you're changing data, you need the option of saving the workbook before running the macro. Step 2 performs this task by displaying a message box that asks if you want to save the workbook first. Your three choices are Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name — Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell as you go through.
After a cell is activated, the macro uses the IsEmpty function to make sure that the cell is not empty. This function improves performance a bit by skipping the cell if it's empty. You then pass the value of that cell to the Trim function, which is a native Excel function that removes leading and trailing spaces.
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
U.S. zip codes come in either 5 or 10 digits. Some systems output a 10-digit zip code, which is too many for a lot of Excel analysis. A common data standardization task is to truncate zip codes to the left five digits. Many of us use formulas to do this, but if you're constantly cleaning up your zip codes, you might want to use the macro outlined in this section to automate the task.
It’s important to note that although this macro solves a specific problem, the concept of truncating data remains useful for many other types of data cleanup activities.
This macro uses the Left function to extract the left five characters of each zip code in the given range:
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Extract the left 5 characters
If Not IsEmpty(MyCell) Then
MyCell = Left(MyCell, 5)
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. MyRange holds the entire target range, and MyCell holds each cell in the range as the macro enumerates through them one by one.
When you run a macro, it destroys the undo stack, so you can’t undo the changes a macro makes. Because you're changing data, you need the option of saving the workbook before running the macro. Step 2 performs this task by displaying a message box that asks if you want to save the workbook first. Your three choices are Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell.
After a cell is activated, Step 5 uses the IsEmpty function to make sure that the cell is not empty. This function improves performance a bit by skipping the cell if it's empty. You then pass the cell’s value through Left function, which allows you to extract out the nth leftmost characters in a string. In this scenario, you need the left five characters to truncate the zip code to five digits.
Step 6 loops back to get the next cell. After all the cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
Many systems require unique identifiers (such as customer number, order number, or product number) to have a fixed character length. For instance, you frequently see customer numbers that look like this: 00000045478. This concept of taking a unique identifier and forcing it to have a fixed length is typically referred to as padding. The number is padded with zeros to achieve the prerequisite character length.
It’s a pain to do this manually in Excel. However, with a macro, padding numbers with zeros is a breeze.
The problem with this solution is that the padding you get is cosmetic only. A quick glance at the formula bar will reveal that the data actually remains numeric without the padding (it does not become textual). So if you copy and paste the data into another platform or non-Excel table, you will lose the cosmetic padding.
Say that all your customer numbers need to be 10 characters long. So for each customer number, you need to pad the number with enough zeros to get it to 10 characters. This macro does just that.
As you review this macro, keep in mind that you need to change the padding logic in Step 5 to match your situation.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Pad with 10 zeros then take the right 10
If Not IsEmpty(MyCell) Then
MyCell.NumberFormat = "@"
MyCell = "
0000000
000" & MyCell
MyCell = Right(MyCell, 10)
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
When you run a macro, it destroys the undo stack, meaning that you can’t undo the changes a macro makes. Because you are actually changing data, you need to give yourself the option of saving the workbook before running the macro. This is what Step 2 does. Here, you call up a message box that asks if you want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range that was selected on the spreadsheet. You can easily set the MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell.
After a cell is activated, Step 5 uses the IsEmpty function to make sure the cell is not empty. You do this to save a little on performance by skipping the cell if there is nothing in it.
The macro then ensures that the cell is formatted as text. This because a cell formatted as a number cannot have leading zeros — Excel would automatically remove them. On the next line, you use the NumberFormat property to specify that the format is @. This symbol indicates text formatting.
Next, the macro concatenates the cell value with 10 zeros. You do this simply by explicitly entering 10 zeros in the code, and then using the ampersand (&) to combine them with the cell value.
Finally, Step 5 uses the Right function to extract out the 10 right-most characters. This effectively gives us the cell value, padded with enough zeros to make 10 characters.
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
In some analyses, blank cells can cause of all kinds of trouble. They can cause sorting issues, they can prevent proper auto filling, they can cause your pivot tables to apply the Count function instead of the Sum function, and so on.
Blanks aren’t always bad, but if they are causing you trouble, this is a macro you can use to quickly replace the blanks in a given range with a value that indicates a blank cell.
This macro enumerates through the cells in the given range, and then uses the Len function to check the length of the value in the active cell. Blank cells have a character length of 0. If the length is indeed 0, the macro enters a 0 in the cell, effectively replacing the blanks.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Ensure the cell has text formatting
If Len(MyCell.Value) = 0 Then
MyCell = 0
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
You first declare two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
When you run a macro, it destroys the undo stack. This means you can’t undo the changes a macro makes. Because you are actually changing data, you need to give yourself the option of saving the workbook before running the macro. This is what Step 2 does. Here, you call up a message box that asks if you want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you are using the selected range — the range that was selected on the spreadsheet. You can easily set the MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell.
After a cell is activated, you use the IsEmpty function to make sure the cell is not empty. You do this to save a little on performance by skipping the cell if it's empty. You then use the Len function, which is a standard Excel function that returns a number corresponding to the length of the string being evaluated. If the cell is blank, the length with be 0, at which point, the macro replaces the blank with a 0. You could obviously replace the blank with any value you’d like (N/A, TBD, No Data, and so on).
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
Every so often, you come upon a situation where you need to attach data to the beginning or end of the cells in a range. For instance, you may need to add an area code to a set of phone numbers. The macro in this section demonstrates how you can automate data standardization tasks that require adding data to values.
This macro uses two Range object variables to go through the target range, leveraging the For Each statement to activate each cell in the target range. Every time a cell is activated, the macro attaches an area code to the beginning of the cell value.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Save the workbook before changing cells?
Select Case MsgBox("Can't Undo this action. " & _
"Save Workbook First?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Step 3: Define the target range
Set MyRange = Selection
'Step 4: Start looping through the range
For Each MyCell In MyRange
'Step 5: Ensure the cell has text formatting
If Not IsEmpty(MyCell) Then
MyCell = "(972) " & MyCell
End If
'Step 6: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. MyRange holds the entire target range, and MyCell holds each cell in the range as you enumerate through them one by one.
When you run a macro, it destroys the undo stack, so you can’t undo the changes a macro makes. Because you're changing data, you need the option of saving the workbook before running the macro. Step 2 displays a message box that asks if you want to save the workbook first. Your three choices are Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
Step 3 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 4 starts looping through each cell in the target range, activating each cell as you go through.
After a cell is activated, you use the ampersand (&) to combine an area code with the cell value. If you need to add text to the end of the cell value, you would simply place the ampersand and the text at the end. For instance, MyCell = MyCell & “Added Text”.
Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
Sometimes your data has nonprinting characters, such as line feeds, carriage returns, and nonbreaking spaces. These characters often need to be removed before you can use the data for serious analysis.
Now, anyone who has worked with Excel for more than a month knows about the Find and Replace functionality. You may have even recorded a macro while performing a Find and Replace (a recorded macro is an excellent way to automate find-and-replace procedures). If so, your initial reaction may be to simply find and replace these characters. The problem is that nonprinting characters are for the most part invisible and thus difficult to clean up with normal Find and Replace routines. The easiest way to clean them up is through VBA.
If you find yourself struggling with those pesky invisible characters, use the general-purpose macro in this section to find and remove all nonprinting characters.
This macro is a relatively simple Find and Replace routine. You use the Replace method, telling Excel what to find and what to replace it with. The syntax is similar to what you would see when recording a macro while manually performing a Find and Replace. The difference is that instead of hard-coding the text to find, the macro uses character codes to specify your search text.
Every character has an underlying ASCII code, similar to a serial number. For instance, the lowercase letter a has an ASCII code of 97. The lowercase letter c has an ASCII code of 99. Likewise, invisible characters also have a code:
This macro utilizes the Replace method, passing each character’s ASCII code as the search item. Each character code is then replaced with an empty string:
Sub Macro1()
'Step 1: Remove line feeds
ActiveSheet.UsedRange.Replace What:=Chr(10), _
Replacement:=""
'Step 2: Remove carriage returns
ActiveSheet.UsedRange.Replace What:=Chr(13), _
Replacement:=""
'Step 3: Remove nonbreaking spaces
ActiveSheet.UsedRange.Replace What:=Chr(160), _
Replacement:=""
End Sub
Step 1 looks for and removes the line-feed character, whose ASCII code is 10. You can identify the code 10 character by passing it through the Chr function. After Chr(10) is identified as the search item, this step passes an empty string to the Replacement argument.
Note the use of ActiveSheet.UsedRange, which essentially tells Excel to look in all the cells containing data. You can replace the UsedRange object with an actual range if needed.
Step 2 finds and removes the carriage-return character.
Step 3 finds and removes the nonbreaking-space character.
To implement this macro, you can copy and paste it into a standard module:
Ever wanted to expose the duplicate values in a range? The macro in this section does just that. You can manually find and highlight duplicates in many ways: using formulas, conditional formatting, sorting, and so on. However, all these manual methods require setup and some level of maintenance as the data changes.
This macro simplifies the task, allowing you to find and highlight duplicates in your data with a click of the mouse, as shown in Figure 7-4.
The macro enumerates through the cells in the target range, leveraging the For Each statement to activate each cell one at a time. You then use the CountIf function to count the number of times the value in the active cell occurs in the range selected. If that number is greater than 1, you format the cell yellow.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Define the target range
Set MyRange = Selection
'Step 3: Start looping through the range
For Each MyCell In MyRange
'Step 4: Ensure the cell has text formatting
If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
MyCell.Interior.ColorIndex = 36
End If
'Step 5: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. MyRange holds the entire target range, and MyCell holds each cell in the range as the macro enumerates through them one by one.
Step 2 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 3 starts looping through each cell in the target range, activating each cell.
The WorksheetFunction object provides a way to run many Excel spreadsheet functions in VBA. Step 4 uses the WorksheetFunction object to run a CountIf function in VBA. In this case, you count the number of times the active cell value (MyCell.Value) is found in the given range (MyRange). If the CountIf expression evaluates to greater than 1, the macro changes the interior color of the cell.
Step 5 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
With the preceding macro, you can quickly find and highlight duplicates in your data. This technique in itself can be quite useful. But if you have many records in your range, you may want to take the extra step of hiding all nonduplicate rows.
Look at the example in Figure 7-5. You can easily see which rows have duplicate values because they are the only rows displayed.
The macro enumerates through the cells in the target range, leveraging the For Each statement to activate each cell one at a time. You then use the CountIf function to count the number of times the value in the active cell occurs in the range selected. If that number is 1, you hide the row in which the active cell resides. If that number is greater than 1, you format the cell yellow and leave the row visible.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Define the target range
Set MyRange = Selection
'Step 3: Start looping through the range
For Each MyCell In MyRange
'Step 4: Ensure the cell has text formatting
If Not IsEmpty(MyCell) Then
If WorksheetFunction.CountIf(MyRange, MyCell) > 1 Then
MyCell.Interior.ColorIndex = 36
MyCell.EntireRow.Hidden = False
Else
MyCell.EntireRow.Hidden = True
End If
End If
'Step 5: Get the next cell in the range
Next MyCell
End Sub
Step 1 declares two Range object variables. MyRange holds the entire target range, and MyCell holds each cell in the range as you enumerate through them one by one.
Step 2 fills the MyRange variable with the target range. In this example, you use the selected range — the range selected on the spreadsheet. You can easily set the MyRange variable to a specific range, such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
Step 3 loops through each cell in the target range, activating each cell as you go through.
In Step 4, you use the IsEmpty function to make sure that the cell is not empty. In this way, the macro won’t automatically hide empty rows in the target range.
You then use the WorksheetFunction object to run a CountIf function in VBA. In this particular scenario, you count the number of times the active cell value (MyCell.Value) is found in the given range (MyRange).
If the CountIf expression evaluates to greater than 1, you change the interior color of the cell and set the EntireRow property to Hidden=False. This step ensures that the row is visible.
If the CountIf expression does not evaluate to greater than 1, the macro jumps to the Else argument. Here you set the EntireRow property to Hidden=True. This ensures the row is not visible.
Step 5 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
To implement this macro, you can copy and paste it into a standard module:
It goes without saying that the AutoFilter function in Excel is one of the most useful. Nothing else allows for faster on-the-spot filtering and analysis. The only problem is that the standard AutoFilter functionality applies drop-down arrows to every column in the chosen data set, as shown in Figure 7-6. This behavior is all right in most situations, but what if you want to prevent your users from using the AutoFilter drop-down arrows on some of the columns in your data?
The good news is that with a little VBA, you can selectively hide AutoFilter drop-down arrows, as shown in Figure 7-7.
In VBA, you can use the AutoFilter object to turn on AutoFilters for a specific range. For instance:
Range("B5:G5").AutoFilter
After an AutoFilter is applied, you can manipulate each column in the AutoFilter by pointing to it. For example, to perform some action on the third column in the AutoFilter:
Range("B5:G5").AutoFilter Field:3
You can perform many actions on an AutoFilter field. In this scenario, you are interested in making the drop-down arrow on field 3 invisible. For this, you can use the VisibleDropDown parameter. Setting this parameter to False makes the drop-down arrow invisible:
Range("B5:G5").AutoFilter Field:3, VisibleDropDown:=False
Here is an example of a macro where you turn on AutoFilters and then make only the first and last drop-down arrows visible:
Sub Macro1()
With Range("B5:G5")
.AutoFilter
.AutoFilter Field:=1, VisibleDropDown:=True
.AutoFilter Field:=2, VisibleDropDown:=False
.AutoFilter Field:=3, VisibleDropDown:=False
.AutoFilter Field:=4, VisibleDropDown:=False
.AutoFilter Field:=5, VisibleDropDown:=False
.AutoFilter Field:=6, VisibleDropDown:=True
End With
End Sub
To implement this macro, you can copy and paste it into a standard module:
Often, when you're working with a set of data that is AutoFiltered, you want to extract the filtered rows to a new workbook. Of course, you can manually copy the filtered rows, open a new workbook, paste the rows, and then format the newly pasted data so that all the columns fit. But if you are doing this sequence frequently enough, you may want to use a macro to speed up the process.
The following macro captures the AutoFilter range, opens a new workbook, and then pastes the data:
Sub Macro1()
'Step 1: Check for AutoFilter and exit if none exists
If ActiveSheet.AutoFilterMode = False Then
Exit Sub
End If
'Step 2: Copy the autofiltered range to new workbook
ActiveSheet.AutoFilter.Range.Copy
Workbooks.Add.Worksheets(1).Paste
'Step 3: Size the columns to fit
Cells.EntireColumn.AutoFit
End Sub
Step 1 uses the AutoFilterMode property to check whether the sheet has AutoFilters applied. If not, you exit the procedure.
Each AutoFilter object has a Range property. This Range property obligingly returns the rows to which the AutoFilter applies, meaning it returns only the rows that are shown in the filtered data set. In Step 2, you use the Copy method to capture those rows, and then you paste the rows to a new workbook. Note that you use Workbooks.Add.Worksheets(1), which tells Excel to paste the data into the first sheet of the newly created workbook.
Step 3 simply tells Excel to size the column widths to autofit the data you just pasted.
To implement this macro, you can copy and paste it into a standard module:
When you have a large table with many columns that are AutoFiltered, know which columns are filtered and which aren’t can be difficult. You could scroll through the columns, peering at each AutoFilter drop-down list for the tell-tale icon indicating that the column is filtered, but that can get old quickly.
The macro in this section helps by specifically listing in the status bar all filtered columns. The status bar runs across the bottom of the Excel window, as shown in Figure 7-8.
The macro loops through the fields in your AutoFiltered data set. As you loop, you check to see if each field is filtered. If so, you capture the field name in a text string. After looping through all the fields, you pass the final string to the StatusBar property:
Sub Macro1()
'Step 1: Declare your variables
Dim AF As AutoFilter
Dim TargetField As String
Dim strOutput As String
Dim i As Integer
'Step 2: Check if AutoFilter exists - if not, exit
If ActiveSheet.AutoFilterMode = False Then
Application.StatusBar = False
Exit Sub
End If
'Step 3: Set AutoFilter and start looping
Set AF = ActiveSheet.AutoFilter
For i = 1 To AF.Filters.Count
'Step 4: Capture filtered field names
If AF.Filters(i).On Then
TargetField = AF.Range.Cells(1, i).Value
strOutput = strOutput & " | " & TargetField
End If
Next
'Step 5: Display the filters if there are any
If strOutput = "" Then
Application.StatusBar = False
Else
Application.StatusBar = "DATA IS FILTERED ON " & strOutput
End If
End Sub
Step 1 declares four variables. AF is an AutoFilter variable that manipulates the AutoFilter object. TargetField is a String variable that holds the field names of any filtered field. strOutput is the String variable you use to build out the final text that appears into the status bar. Finally, the i variable serves as a simple counter, allowing you to iterate through the fields in your AutoFilter.
Step 2 checks the AutoFilterMode property to see if a sheet even has AutoFilters applied. If not, you set the StatusBar property to False, which has the effect of clearing the status bar, releasing control back to Excel. You then exit the procedure.
Step 3 sets the AF variable to the AutoFilter on the active sheet. You then set your counter to count from 1 to the maximum number of columns in the AutoFiltered range. The AutoFilter object keeps track of its columns with index numbers. Column 1 is index 1; column 2 is index 2, and so on. The idea is that you can loop through each column in the AutoFilter by using the i variable as the index number.
Step 4 checks the status of AF.Filters object for each (i), where i is the index number of the column you're evaluating. If the AutoFilter for that column is filtered in any way, the status for that column is On.
If the filter for the column is on, you capture the name of the field in the TargetField variable. You actually get the name of the field by referencing the Range of your AF AutoFilter object. With this range, you can use the Cells item to pinpoint the field name. Cells(1,1) captures the value in row one, column one. Cells(1,2) captures the value in row one, column two, and so on.
As you can see in Step 4, you've hard-coded the row to 1 and used the i variable to indicate the column index. As the macro iterates through the columns, it always captures the value in row one as the TargetField name (row one is where the field name is likely to be).
After you have the TargetField name, you can pass that information to a simple string container (strOutput in your case). strOutput keeps all target field names you find and concatenates them into a readable text string.
Step 5 first checks to make sure that something is in the strOutput string. If strOutput is empty, the macro did not find any filtered columns in your AutoFilter . In this case, Step 5 simply sets the StatusBar property to False, releasing control back to Excel.
If strOutput is not empty, Step 5 sets the StatusBar property to equal some helper text along with your strOutput string.
You ideally want this macro to run each time a field is filtered. However, Excel does not have an OnAutoFilter event. The closest thing to that event is the Worksheet_Calculate event. That being said, AutoFilters in themselves don’t calculate anything, so you need to enter a volatile function on the sheet that contains your AutoFiltered data. A volatile function forces a recalculation when any change is made on the worksheet.
In the sample files that come with this book, note that you use the Now function. The Now function is a volatile function that returns a date and time. With this function on the sheet, the worksheet is sure to recalculate each time the AutoFilter is changed.
Place the Now function anywhere on your sheet by typing =Now() in any cell. Then copy and paste the macro in the Worksheet_Calculate event code window as follows:
To make the code run as smoothly as possible, consider adding the following two pieces of code under the worksheet calculate event:
Private Sub Worksheet_Deactivate()
Application.StatusBar = False
End Sub
Private Sub Worksheet_Activate()
Call Worksheet_Calculate
End Sub
Also, add this piece of code in the workbook BeforeClose event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = False
End Sub
The Worksheet_Deactivate event clears the status bar when you move to another sheet or workbook, avoiding confusion as you move between sheets.
The Worksheet_Activate event fires the macro in Worksheet_Calculate. This event brings back the status bar indicators when you navigate back to the filtered sheet.
The Workbook_BeforeClose event clears the status bar when you close the workbook, avoiding confusion as you move between workbooks.
18.191.33.136