Set databodyrange of filtered table to include ALL cells

[mikeincairns2]

I need to format ALL the cells in a filtered table (even the hidden ones).

I have tried

Sub test()
Dim myRange As Range

Set myRange = Sheets(1).ListObjects(1).DataBodyRange
myRange.WrapText = True

End Sub

Can anyone please help?

 

[mikeincairns2]

Thanks everyone for the help. Here's a few things I noted while wasting way too much time on this issue.
Rick was (of course) 100% correct with all his advice. Nearly all the confusion was due to user error (Me).

1. Firstly there is no difference between 2010 and 2016 versions. Ricks code works well in both versions.
2. Secondly the code does select the entire table body even in a filtered list (but see the third item)
3. Here is what threw me. The code behaves differently if the active cell is hidden in a filtered table
If the active cell is hidden (in a hidden row due to filtered table) then the code will only affect the visible cells (ie not the filtered cells)
If the active cell is visible then the code will effect all the table body (including the filtered cells)

This seems like a bug to me. It took a lot of time for me to discover this so hopefully it is useful to someone else.

So here is the code that I settled on:

Sub WrapTextToggle()
  'If activecell is in a hidden row move to the table header
  If Selection.EntireRow.Hidden Then Sheets(1).ListObjects(1).HeaderRowRange(1).Select
  
  'Toggle Wrap text
  With Sheets(1).ListObjects(1).DataBodyRange
    .WrapText = Not .WrapText
  End With
End Sub

 

[Levi B]

Hello zombie thread. Just adding another solution that does not require changing the selection:

Sub WrapTextToggle
    With Sheets(1).ListObjects(1).DataBodyRange
        .HeaderRowRange.Offset(1).Resize(.DataBodyRange.Rows.Count).WrapText = _
            Not (.HeaderRowRange.Offset(1).Cells(1).WrapText)
    End With
End sub

 

[Levi B]

After more testing on different computers, I have been able to replicate the but that Mike mentioned when using my code - so my addition was not helpful at all. I either don't have or can't find editing rights to correct my immediately previous post. Please refer to Mike's post above.

 

[Levi B]

After further testing, I have found that even under the condition mentioned by Mike (i.e. if the active cell is visible), I still could not rely on it effecting all the table body. Short of looping through each row in the table, the best I reliably managed was to always affect the visible cells - which is fortunately sufficient for my purposes. The following code was enough to do that:

Sub WrapTextToggle()
    Dim blnWrapText As Boolean
    Dim lngRows As Long
    With Sheets(1).Range("Table1")
        lngRows = .Rows.Count
        On Error GoTo Abort         'In case none of the cells in the table are visible
            blnWrapText = Not (.SpecialCells(xlCellTypeVisible).Cells(1).WrapText)
        On Error GoTo 0
        .WrapText = blnWrapText     'This line is unreliable. It is included in the hopes of Excel fixing the bug that necessitates the following line.
        .SpecialCells(xlCellTypeVisible).WrapText = blnWrapText
    End With
    Beep
Abort:
End Sub