Need help to auto re-size filtered rows

[mickhanson]

Hi,

I have a presentation only workbook that draws info from other sources which are dynamic

i have a VBA code therefore that fires when the workbook is opened to autofit the new data into the row heights.

The problem that i have is:- if a filter is active when the workbook is opened, the rows in the filtered out cells do not re-size and can potentially hide data when the filter is altered or removed.

Is there a way of ensuring that the filtered out rows are auto resized when a filter is changed?

I have tried code that would recognise a change in the workbook but filter changes won't make it fire.

Thanks in advance

Mick

[cubangt]

when you want to adjust the size, this this:

Worksheets("Sheet1").Range("A1:E1").Columns.AutoFit

Just make sure to provide the correct range and it will adjust the sizes

https://docs.microsoft.com/en-us/off....range.autofit example found here


Also take a look at this site, offers a few other ways to accomplish

https://excelchamps.com/vba/autofit/

[mickhanson]

sorry this doesn't work. it re-sizes everything except cells which have applied filters

[cubangt]

So if you update the range to something like this it doesnt work?

Worksheets("Sheet1").Range("A2:Z200").Columns.AutoFit

Or even this

ActiveSheet.UsedRange.EntireColumn.AutoFit

ActiveSheet.UsedRange.EntireRow.AutoFit

[mickhanson]

Sorry no,

When i open the work book all rows re-size as dictated by the code. However Any row which contains filtered out data does not autofit. When i remove the filter the rows are the same size as when the document was closed. I have tried many times and with many different varieties of the same code but they all ignore filtered cells.

The only thing i can think of would be to write code to remove all of the filters but then i would have to manually re-apply them all.

It is quite a complicated set of column data and i could have cross filters applied over 4 or 5 columns so i didn't want to do this if i didn't have to.

I am aware that i could write data count cells and use VBA to monitor changes in the data count and use that to fire the re-size but this seems quite complicated thing to do for what i see as a simple need.

What i really need is code that would recognise any filter change on the sheet.

I have a workaround at the minute. I have a macro which is linked to a form control that i can press every time i alter a filter but every now and again i forget and print the sheet with hidden data.

It's quite frustrating.

[cubangt]

Can you provide a sample file that demonstrates these issues?
Steps can be found up top in the yellow banner.

[mickhanson]

attached is a very basic example of that i am talking about.

The code is set to re-size the rows in the workbook. when the book is opened it does just that. However row 9 is hidden by a filter which excludes the data within it. As soon as the filter is removed you will see that row 9 has not been re-sized for the data

[cubangt]

Now i will say, that the autofit looks at all data in the columns and rows even if they are filtered and not seen on the screen.

So if you have a cell with text like this:
" Hello, today is Wednesday and its going to be great"

And then you have in the same column some text like this:
"Hello"

And your filtered data is only showing the "Hello", auto fit wont adjust to that smaller size, it will autofit to the larger text.

If thats what you are experiencing then maybe you will need to get the "size" of the largest cell in the filtered data and set the columnwidth to that value in your results.

[cubangt]

Are you wanting to keep that "Word Wrap" set on that column or other columns in your workbook?

[cubangt]

So yea, my post #8 is whats happening with your sample data.
Even though you have a value in the filtered data that is smaller than the rows that are hidden, the autofit will adjust to the largest in the column regardless if its visible or not.

So if you want it a certain size you can keep the word wrap and then just set the column width after filtering to a certain size.

[mickhanson]

i need it to auto fit the largest data set and it does this exactly correctly for all rows except those which are filtered out. In reality it doesn't alter the size of the row at all it just ignores it.

[mickhanson]

take a look at the attached example where i have altered the data set. each row re-sizes on opening to fit the contents of the cell. except for the filtered out cell which doesnot re-size

[cubangt]

Maybe im not understanding clearly, but im trying..

Is it the rows in green that you are trying get to resize?

[mickhanson]

No,

in the screen shot you have sent you have cleared the filter because i can see row 9 and the words "sorry this"

this cell contains much more than these 2 words and if it had been re-sized by the code i would see the words "sorry this doesn't work at all". All the other cells which contain words re-sized to suit their content except row 9 which was hidden by the filter. when you removed the filter it has not resized this row.

Thanks for your help with this

[cubangt]

Then im really confused, i just unselected that row and it shows all the words for me, without me manually expanding the cell size. Is that what you need?

[mickhanson]

the first picture you sent is what i see after the code fires showing just the words "sorry this",

did you save the work book somewhere after opening the filter because then the code would fire on all rows as they would all be visible.

I have attached a series of pictures on what i am seeing.

Capture 1
If you open the workbook on post 12 again you will see all of the rows at the excel default height.
As soon as you enable content the code fires and the rows are resized

capture 2
this shows the rows resized but the row with "sorry this doesn't work at all" is hidden

Capture 3
this show what i see when i remove the filter. You can see in this image that the code has not autofit the hidden row.

if i now saved this workbook and open it again, then yes it would expand this row because it would be no longer hidden