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?

 

[vcoolio]

G'day Mike,

What's the weather like in Cairns today?

You may only need the following to do the task for you:-

Sub test()

Sheet1.ListObjects("Table1").DataBodyRange.WrapText = True  '---->Assumes that the table name is "Table1". Change to suit.

End Sub

I hope that this helps,

Cheerio,
vcoolio.

 

[AlphaFrog]

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

Can I ask why you want to do it on a filtered table but include then hidden cells for the wrap text? Why not just unfilter the table, or at least have the code do it, then re-apply the filter.

 

[mikeincairns2]

Sorry vcoolio, doesn't work on a filtered list. I need to format ALL the cells in the databodyrange (those hidden and those not hidden)
BTW Beautiful wether in Cairns

G'day Mike,

What's the weather like in Cairns today?

You may only need the following to do the task for you:-

Sub test()

Sheet1.ListObjects("Table1").DataBodyRange.WrapText = True  '---->Assumes that the table name is "Table1". Change to suit.

End Sub

I hope that this helps,

Cheerio,
vcoolio.

 

[mikeincairns2]

Yes I realise that I can "un-filter" and then "re-filter". But I just thought there would be an easy way to set range of all cells.

Scenario: We have a table where some cells have a lot of text. We have a macro button to display the table in Condensed (no text-wrap) or Detailed (with text wrap). Users often filter the table to their needs. Want the button to work regardless of filtered or not filtered.

It is a pain to set variables to how the user has filtered, then in-filter, do something, and then re-apply the user's filters. That seems very inefficient.

 

[Rick Rothstein]

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?

Does this toggle (run it to wrap/run again to unwrap) code do what you want...

Sub WrapTextToggle()
  Dim Addr As String
  Addr = Sheets(1).ListObjects(1).DataBodyRange.Address
  Range(Addr).WrapText = Not Range(Addr).WrapText
End Sub

 

[AlphaFrog]

It is a pain to set variables to how the user has filtered, then in-filter, do something, and then re-apply the user's filters. That seems very inefficient.

I understand, but it may be the only way to do it as far as I know.

 

[mikeincairns2]

Thanks Rick
That's a really clever solution, that should work...but...unfortunately does not.
I cant understand why though. The Addr variable does, in fact, return the correct range (ie the visible and hidden cells), but for some reason the formatting still only happens on the visible cells in the table.

 

[Rick Rothstein]

Thanks Rick
That's a really clever solution, that should work...but...unfortunately does not.
I cant understand why though. The Addr variable does, in fact, return the correct range (ie the visible and hidden cells), but for some reason the formatting still only happens on the visible cells in the table.

When I tested it on my XL2010 workbook, it appeared to work fine for me... first I used the drop down arrow on the header to hide some unwrapped cells, then I ran the macro and then when I used the drop down arrow on the header cell and set the filter to show all the rows, both the visible and previously hidden cells had wrapped text.

 

[mikeincairns2]

Not for me. Excel 2016 using Data Tables (specifically) not a list/range.
I also tried other formatting and it did not work on the hidden cells.