I’m a sucker for a good western. Here’s John Wayne as Rooster Cogburn in True Grit the only film for which Wayne won the Oscar for Best Actor. Wayne and a partner owned a ranch not far from here and tales spin around the old town of Wayne and actor buddy, Lee Marvin, closing down a local cantina many a night back in the day.

But I digress, as this post is not about life out here in the open range of the wild west. Rather, it is about the Range Object of the Excel Object Model. The Range Object has 78 Methods and 96 Properties. I’ll take a look at some of these over a series of posts. I’ll begin with these methods:

  1. AdvancedFilter
  2. AutoFilter
  3. Find
  4. SpecialCells
  5. PasteSpecial


Here’s a code snippet of the final code at the bottom that demonstrates some of the syntax for the AdvancedFilter Method:

Range.AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=wsCriteria.Cells(1, 1), _

The most important parameters here are the Action and Unique parameters. This allows us to quickly get a unique list of data from the dataset that we can use as the criteria for the AutoFilter later


Here’s a code snippet of the final code at the bottom that demonstrates some of the syntax for the AutoFilter Method:

    Range.AutoFilter _
             Field:=lngField, _

There are additional parameters available for the AutoFilter Method and I encourage you to investigate those. See the link at the bottom of this post. For now, I want to point out that I am passing variables to the named parameters. In the next section you’ll see how I use the Find Method to determine the column number of the header that I am interested in as “lngField”.

As I loop though the list of criteria created in the AdvancedFilter process I pass the value as “varCriteria” to the AutoFilter process.


Here’s a code snippet of the final code at the bottom that demonstrates some of the syntax for the Find Method:

lngField = rngHeader.Find(What:=strFieldName, _
                          LookIn:=xlValues, _
                          LookAt:=xlWhole, _

The Find Method allows us to get a value at run-time using an InputBox for example, find the item, and pass the column number where the item is located to our process. This makes the process more robust and dynamic.


Here’s a code snippet of the final code at the bottom that demonstrates some of the syntax for the SpecialCells Method:


When most folks begin using VBA, they tend to loop through cells. However, SpecialCells used in conjunction with AutoFilter can offer much significant performance improvements as looping through cells tends to be slower. There are many types (enumeration) of SpecialCells. Link at bottom for full list. In the code sample below, I am using xlCellTypeVisible thereby ignoring all rows hidden by the AutoFilter.


Here’s a code snippet of the final code at the bottom that demonstrates some of the syntax for the PasteSpecial Method:

wbBifurcate.Worksheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats

There are 11 other paste types in the enumeration. I encourage you to explore them all. See the link at the bottom of this post.

Case Study: Bifurcate Excel File

ExcelSplitA common question posted on the LinkedIn Groups is, “How to split (bifurcate) an Excel file based on some criteria within the file?”

We need some test data so I downloaded a random data generator add-in over at Daily Dose of Excel by Dick Kusleika and whipped up some data:
 Looks good so far. Now I’d like to bifurcate the master data into separate files based on the company name.

Get a unique list of company names

I’ll leverage the “unique” parameter of the AdvancedFilter Method of the Range Object

Range.AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=wsCriteria.Cells(1, 1), _


The Complete Code

Option Explicit

Sub BifurcateFile()

    'Author:Winston Snyder
    'Date: 12/7/2013
    'Purpose: Bifurcate master file into component files
    'Declare variables
        Dim wb As Workbook
        Dim wbBifurcate As Workbook
        Dim ws As Worksheet
        Dim wsCriteria As Worksheet
        Dim rngList As Range
        Dim rngData As Range
        Dim rngCriteria As Range
        Dim rngHeader As Range
        Dim r As Long
        Dim c As Long
        Dim i As Long
        Dim lngField As Long
        Dim rCriteria As Long
        Dim strFieldName As String
        Dim strPath As String
        Dim varCriteria As Variant
    'Excel environment - speed things up
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
    'Intialize variables
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Data")
        strFieldName = InputBox("Please enter the term to search for?") '<-Update as needed
        strPath = DocsPath & "Load Files\" '<-File output, update as needed
        r = ws.Cells(Rows.Count, 1).End(xlUp).Row
        c = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    'Add a worksheet to hold filtered data
    'This list will become the criteria list for bifurcating the master file
        Set wsCriteria = wb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
    'Find column number to be filtered
        Set rngHeader = ws.Range(ws.Cells(1, 1), ws.Cells(1, c))
        lngField = rngHeader.Find(What:=strFieldName, _
                                  LookIn:=xlValues, _
                                  LookAt:=xlWhole, _
    'Define the range to be filtered
        With ws
            If .FilterMode = True Then
            End If
            Set rngList = .Range(.Cells(1, lngField), .Cells(r, lngField))
            Set rngData = .UsedRange
        End With
    'Filter the range
        With rngList
            .AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=wsCriteria.Cells(1, 1), _
        End With
    'Define the criteria range
    'Begin with row 2 to ignore the header row
        With wsCriteria
            rCriteria = .Cells(Rows.Count, 1).End(xlUp).Row
            Set rngCriteria = .Range(.Cells(2, 1), .Cells(rCriteria, 1))
        End With
    'Loop through criteria range
    'Use each value as criteria to apply to autofilter for data range
    'Create file
        For i = rngCriteria.Rows.Count To 1 Step -1
            varCriteria = rngCriteria.Cells(i, 1).Value
            'Add a workbook to hold filtered results
                Set wbBifurcate = Workbooks.Add
            'Filter the original data
                With ws
                    'If data is filtered, remove filter
                        If .FilterMode = True Then
                        End If
                    'Filter the data
                        .AutoFilterMode = False
                        If Not .AutoFilterMode Then
                            rngData.AutoFilter _
                                Field:=lngField, _
                        End If
                    'Copy the visible range of the data range - include the header row
                        wbBifurcate.Worksheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
                    'Remove the autofilter
                        If .AutoFilterMode = True Then
                            .AutoFilterMode = False
                        End If
                End With
            'Save the bifurcated data workbook
                wbBifurcate.SaveAs strPath & varCriteria & ".xlsx", FileFormat:=51
            'Close the workbook
        Next i
    'Tidy up
        'Delete temporary worksheet
        'Delete objects
            Set rngList = Nothing
            Set rngData = Nothing
            Set rngCriteria = Nothing
            Set rngHeader = Nothing
            Set wsCriteria = Nothing
            Set ws = Nothing
            Set wbBifurcate = Nothing
            Set wb = Nothing
        'Excel environment - restore
            With Application
                .ScreenUpdating = True
                .DisplayAlerts = True
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
            End With
End Sub
Public Function DocsPath() As String
    'Purpose: Get the Environ value for User Docuents
    'Returns: C:\Users\%User Name%\Documents\
    DocsPath = Environ$("USERPROFILE") + "\Documents\"
End Function

I’m using the DocsPath function to get the Documents folder based on which user is logged in. This makes the code a bit more robust and transportable. I should really check for the existence of a subfolder or offer the user the option to create the subfolder if it does not exist – but I feel that complicates the code which may already be complicated enough.

Tidy up

    Final Thoughts

    The sample does take a little bit of time to run as there are 100K records and 100 unique company names – therefore 100 separate files. I’ve seen varying attempts to use passwords and other techniques to allow a user to only view certain data. However, the best way to make sure a user cannot access data that you do not want them to see is to make sure it is not in the workbook in the first place.

    The process may be faster if instead of looping through a range, we first transfer the range to an array and then loop the array to pass the elements of the array as criteria for the autofilter.

    Another option might be to get the size of each range after the autofilter, set the size of the receiving range and then transfer the value of one range to another. This would bypass the Windows Clipboard which is causing the process to run a little slower using the Copy-PasteSpecial. Something like:

    Set rngSource = Range.SpecialCells(xlCellTypeVisible)
    With rngSource
         rowsSource = .Rows.Count
         columnsSource = .Columns.Count
    End With
    Set rngDestination = wbBifurcate.Worksheets("Sheet1").Range("A1")
    Set rngDestination = rngDestination.Resize(rowsSource,columnsSource)
    rngDestination.Value = rngSource.Value

    The process I use above in the main code, may be used in conjunction with varying methods of automated distribution such as through Outlook or TransferSharePointList. Additionally, instead of pasting values into a new workbook, I could use a template workbook that has Pivot Tables, Charts, Formulas – everything that is needed – just need to append the new data or delete any old data and paste in the new data. I’ll post samples of those processes in future posts.


    Download the workbook from SkyDrive.

    Additional Resources

, ,

only 1 comment untill now

  1. […] Method of the Range Object here to copy just the visible range. I covered this previously in ,this […]

Add your comment now