How the macro works

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

Keep in mind that the ranges and sheet names specified here are for demonstration. Alter them to suit the data in your worksheet.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

Because you define the target range as the current selection, you want to be sure to select the area where your data exists before running this code. You don't want to select the entire worksheet because every empty cell in the spreadsheet would be filled with a zero. To ensure that you don't have this problem, explicitly define the target range, such as Set MyRange = Range(“A1:Z100”).

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

As you may have guessed, you can also use the Right function to extract out the nth right-most characters in a string. As an example, it’s not uncommon to work with product numbers where the first few characters hold a particular attribute or meaning, whereas the last few characters point to the actual product (as in 100-4567). You can extract out the actual product by using Right(Product_Number, 4).

Because you define the target range as the current selection, be sure to select the area where your data exists before running this code. In other words, you wouldn’t want to select cells that don’t conform to the logic you placed in this macro. Otherwise, every cell you select is truncated, whether you mean it to be or not. Of course, you can ensure this is never a problem by explicitly defining the target range, such as Set MyRange = Range(“A1:Z100”).

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11 on your keyboard.
2. Right-click project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11 on your keyboard.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

Because you define the target range as the current selection, you want to be sure to select the area where your data exists before running this code. That is to say, you wouldn’t want to select the entire worksheet. Otherwise, every empty cell in the spreadsheet would be filled with a zero. You can ensure that this is never a problem by explicitly defining a range, such as Set MyRange = Range(“A1:Z100”).

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11 on your keyboard.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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:

• The line-feed character code is 10.
• The carriage-return character code is 13.
• The nonbreaking-space character code is 160.

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.

The characters covered in this macro are the most common nonprinting characters. If you work with others, simply add a new line of code, specifying the appropriate character code. You can enter “ASCII Code Listing” in any search engine to see a list the codes for various characters.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert   ⇒   Module.
4. Type or paste the code.

How the macro works

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.

Because you define the target range as the current selection, you want to be sure to select the area where your data exists before running this code. You wouldn’t want to select an entire column or the entire worksheet. Otherwise, any cell that contains data that is unique (not duplicated) triggers the hiding of the row. Alternatively, to ensure that this is never a problem, you can explicitly define the target range — such as Set MyRange = Range(“A1:Z100”).

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code.

How the macro works

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

You are not only pointing to a specific range but also explicitly pointing to each field. When implementing this type of macro in your environment, alter the code to suit your particular data set.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code.

How the macro works

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.

How to use the macro

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:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.
3. Click the sheet from which you want to trigger the code.
4. Select the Calculate event from the Event drop-down list, as shown in Figure 7-9.
5. Type or paste the code.

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.