thewindata

My grasp of all things maritime is limited:

1. I love seafood (Scallops with garlic, butter and white wine – I’m looking at you)
2. I can still tie a few knots I learned in Scouts
3. I enjoy The Pirates of the Caribbean Ride at Disney World
4. I can speak like a pirate. (International Speak Like a Pirate Day is Friday, September 19, 2014. So set yer reminder on yer calendar on yer mobile device now matey, arrrrgghhh!)

Today’s post, however, is not about my favorite ale or the Jolly Roger. It is about the ListObjects Object and / or ListObject Object of the Excel Object Model. There is no typo there – though admittedly it does look and sound a little odd. Links to the Object Models are at the bottom of this post.

Convert a ListObject To A Range

I’ll begin by converting an Excel Table (ListObject) that I created in my last post back to a Range. Warning: Converting an Excel Table to a Range will wreak havoc on any Structured Reference formulas that are using the Excel Table Do not try this on any production models, use test workbooks only until you have a full understanding on the outcomes.

Unlist The Excel Table

Option Explicit

Sub ConvertListObjectToRange_1()
    'Purpose:   Unlist an Excel Table to convert it to a range

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lo As ListObject
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    
    For Each lo In ws.ListObjects
        lo.Unlist
    Next lo
    
    Set ws = Nothing
    Set wb = Nothing

End Sub

1. The filter arrows are gone
2. When I click on a cell within the Range, the Table Tools Tab no longer activates

The Excel Table has been converted to a Range. But I would also like to remove all formatting that was introduced by the Excel Table.

Remove Formatting Left By Excel Table

I would like to remove the these formats:

1. Remove all color
2. Remove all borders
3. Make sure all font colors are black
4. Make sure all fonts are normal (not bold)

Sub RemoveFormatting()
    'Purpose:   Remove formatting left from Excel Table

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rng = ws.Range("A1").CurrentRegion
    
    With rng
        .Interior.ColorIndex = xlColorIndexNone
        .Borders.LineStyle = xlLineStyleNone
    End With
    
    With ws
        .Range("A1").EntireRow.Font.Color = vbBlack
        .Range("A1").EntireRow.Font.Bold = False
    End With
    
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Looks good – all cleaned up! Next, I’ll look at adding a ListObject (Excel Table) using VBA

A Quick Segue – Table Styles

In my code for adding the Excel Table, I will want to add a Table Style so let’s look at some code to list available styles:

Sub ListTableStyles()
    
    'Purpose:   List all Table Styles in the workbook
    'Comment:   [Ctrl] + [G] to activate the immediate window to view the output

    Dim wb As Workbook
    Dim ts As TableStyle
    
    Set wb = ActiveWorkbook
    
    With wb
        For Each ts In .TableStyles
            Debug.Print ts.Name
        Next ts
    End With
    
    Set wb = Nothing
    
End Sub

Now I have a list of all available styles in the workbook as well as the correct naming convention to be used.

Convert A Range To A ListObject (Excel Table)

Now I am ready to convert the Range back to a ListObject (Excel Table)

Sub ConvertRangeToTable()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim lo As ListObject
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rng = ws.Range("A1").CurrentRegion

    Set lo = ws.ListObjects.Add(SourceType:=xlSrcRange, _
                                Source:=rng, _
                                TableStyleName:="TableStyleMedium4", _
                                Destination:=Range("A1"))
    lo.Name = "tblData"
                                
    Set lo = Nothing
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
                                       
End Sub

Looks great! I now have an Excel Table named, “tblData”. There are additional Source Types that may be used as a source for the Excel Table: Source Type Enumeration. I will look at these in future posts.

ListObject Business Case

A fairly common request over on the LinkedIn Excel Groups is how to copy filtered data without the header row to another Worksheet or Range. This can be accomplished with a Range Object and a few manipulations to shape the data. But it is much easier with ListObjects.
The ListObject offers three distinct Ranges that may be exploited in VBA:

The HeaderRowRange

The DataBodyRange

The TotalsRowRange

For today, I will focus on the DataBodyRange. I want to filter the data and copy the Rows that remain except for the HeaderRowRange.

Sub FilterTable()
    'Purpose:   Filter Excel Table, copy visibile range

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim lo As ListObject
    Const strCriteria As String = "Aetna"
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rngDestination = ws.Range("G3")
    
    With ws
        For Each lo In .ListObjects
            'DataBodyRange to Range
                Set rng = lo.DataBodyRange
            
            'Filter the Range
                rng.AutoFilter _
                    Field:=1, _
                    Criteria1:=strCriteria
                    
            'Copy the visible range
                rng.SpecialCells(xlCellTypeVisible).Copy
                rngDestination.PasteSpecial xlPasteValuesAndNumberFormats
        Next lo
    End With

    Set rngDestination = Nothing
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Looks great! Quite a bit easier than jumping through some gyrations to reshape the data to remove the header row from the visible range.

I use the SpecialCells Method of the Range Object here to copy just the visible range. I covered this previously in ,this post

Tidy Up

Final Thoughts

That’s it for today. I hope you find this post helpful and are able to go through fewer gyrations in the future to shape your data. Now, where’d I put my parrot and wooden leg?

Downloads

Download the file from Skydrive

Additional Recources

ListObject Object Model
ListObjects Object Model
Working with Tables in Excel 2013, 2010 and 2007 (VBA)
Working with Microsoft Excel tables in VBA