Fitting columns to text in autofiltered rows

[El_Tel]

I've written some VBA to create a new sheet based on a 'master' sheet then autofilter the new sheet according to certain criteria. This obviously hides some of the unwanted rows leaving the text in other rows visible.

I now want to change the width of some columns so that they will fit the text in the VISIBLE rows only. I thought this would be easy but I can't find a solution. I've tried Autofit but this resets the column width to fit the text in both visible and hidden rows. This leaves me with a lot of white space.

Can anyone help me on this please? I've googled the web without much joy.

 

[Peter_SSs]

I've written some VBA to create a new sheet based on a 'master' sheet then autofilter the new sheet according to certain criteria. This obviously hides some of the unwanted rows leaving the text in other rows visible.

I now want to change the width of some columns so that they will fit the text in the VISIBLE rows only. I thought this would be easy but I can't find a solution. I've tried Autofit but this resets the column width to fit the text in both visible and hidden rows. This leaves me with a lot of white space.

Can anyone help me on this please? I've googled the web without much joy.

Welcome to the MrExcel board!

I'm not sure of a direct or perfect way to do this, but see if this might be good enough. It may fall down depending on the font/character widths involved, but you may be able to compensate enough by adding a little 'extra' where indicated.

I have done this with just one column of interest (col C) but you should be able to adapt for more if required.

1. Set up two 'helper columns as I have done in columns H:I. Copy the col H formula down further than you are likely to want (though I guess this code - or better still, your existing code - could be adding/deleting these formulas as required).

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	H1	H2	H3	H4	H5			2	AAAAAAAAAA
2			a					1
3			ab					2
4			AAAAAAAAAA					10
5			abcd					4
6			lkjuy					5
7			dsgtrih					7
8			lkjhgfr					7
9			lioujhyt					8
10			hgfrtdert					9
11								0
12								0
AutoFit with AutoFilter

2. Hide columns H:I

3. Put this Worksheet_Calculate event code in the sheet module.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("I")<br>        .AutoFit<br>        Columns("C").ColumnWidth = .ColumnWidth <SPAN style="color:#007F00">'<-- Add extra here?</SPAN><br>        .Hidden = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>