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?

 

[Rick Rothstein]

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.

I created my table by clicking the Table button on the Tables panel on the Insert tab, selecting the range, putting a check mark in the "My table has headers" checkbox and clicking the OK button. Is that what you did also?

 

[Rick Rothstein]

I created my table by clicking the Table button on the Tables panel on the Insert tab, selecting the range, putting a check mark in the "My table has headers" checkbox and clicking the OK button. Is that what you did also?

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.

Read the above (what I eventually posted in Message #9 ) as I had edited it around the same time you responded with Message #10 , so you may have missed the additional description. With that said, there may be a difference between XL2010 and XL2016 tables as the following worked (as described above) directly in my copy of Excel...

Sub WrapTextToggle()
  With Sheets(1).ListObjects(1).DataBodyRange
    .WrapText = Not .WrapText
  End With
End Sub

 

[vcoolio]

Hi Mike,

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

I've attached the little sample that I tested the code in from my first response at the following link:-

http://ge.tt/65uuUUp2

You'll see that it works in the sample for the whole data body, filtered or not, and, if you hide say column E, it still works. Click on the "RUN" button to execute the code.
I don't understand why it won't work in your workbook.

I also tried Rick's code in the sample but that did not work (I'm using 2016) although a little quirk in Rick's code on this line:-

.WrapText = Not .WrapText

could be the reason. You'll note that Excel forces a space just after "Not" meaning that it is not accepting the line as being suitably qualified. I'll leave that to Rick.

AlphaFrog's suggestion may be the way to go if all else fails.


Cheerio,
vcoolio.

 

[mikeincairns2]

Thanks vcoolio - but perhaps you aren't understanding what I need. I am confused by your comment "hide column E".
I took your sample and filtered column H to only show blanks. This has the result to hide row 12. I then ran your code. It formatted every cell as wrap text except the hidden row 12.
So - sorry - still no solution.

 

[mikeincairns2]

Read the above (what I eventually posted in Message #9 ) as I had edited it around the same time you responded with Message #10 , so you may have missed the additional description. With that said, there may be a difference between XL2010 and XL2016 tables as the following worked (as described above) directly in my copy of Excel...

Sub WrapTextToggle()
  With Sheets(1).ListObjects(1).DataBodyRange
    .WrapText = Not .WrapText
  End With
End Sub

Well here is something interesting. After a lot of testing I am confident to say there is a difference in Excel versions. I was able to get your code to work in Excel 2010. I then emailed it to an Excel 2016 device and it does not work !!
Thanks a lot Rick, but looks like I might have to go to a more complex solution to get things working in Excel 2016.

 

[vcoolio]

Hi Mike,

"hide column E".

Sorry, I had assumed that you were hiding cells by hiding certain columns.

However, I did the same as you in my sample from my personal file with row 12 and it worked just fine. So now I'm at a total loss as to why it still doesn't work for you. Is there something else that we need to know; e.g. protected sheet?

Cheerio,
vcoolio.

 

[Rick Rothstein]

I also tried Rick's code in the sample but that did not work (I'm using 2016) although a little quirk in Rick's code on this line:-

.WrapText = Not .WrapText

could be the reason. You'll note that Excel forces a space just after "Not" meaning that it is not accepting the line as being suitably qualified. I'll leave that to Rick.

Just so you know, that line of code is syntactically correct... the WrapText property stores a Boolean value of True or False and Not is a logical operator which reverses the Boolean value following it.

 

[Rick Rothstein]

Well here is something interesting. After a lot of testing I am confident to say there is a difference in Excel versions. I was able to get your code to work in Excel 2010. I then emailed it to an Excel 2016 device and it does not work !!
Thanks a lot Rick, but looks like I might have to go to a more complex solution to get things working in Excel 2016.

That is a little disturbing to hear... I cannot think of a good (or even bad) reason for Microsoft to have changed what should be such a basic (no pun intended) functionality.

 

[vcoolio]

Just so you know, that line of code is syntactically correct... the WrapText property stores a Boolean value of True or False and Not is a logical operator which reverses the Boolean value following it.

Yes Rick, I'm fully aware of that but thanks for the prompt anyway.

The question is:

Why does your code not work on my laptop (2010) or my PC (2016) yet my code does?

I'd appreciate any insights that you could offer as I'm sure Mike would also.

Cheerio,
vcoolio.

 

[Rick Rothstein]

The question is:

Why does your code not work on my laptop (2010) or my PC (2016) yet my code does?

I'd appreciate any insights that you could offer as I'm sure Mike would also.

Neither of my posted codes works on your XL2010 laptop? Given that they both work for me, I really don't have an insight on what the problem could be.