Change column width based on empty cells

[vascobmcastro]

Hi,

I have a table that ranges from columns G:AF. Row 3 (G3:AF3) has text which can be empty or not. How could I set a full column width for the columns that show empty values in the G3:AF3 range?

Thank you

Vasco

 

[JLGWhiz]

Columns("G:AF").ColumnWidth = 25

would set all of those columns to width of 25 points. Presence or absence of data is not relevant to setting column width and row height. However, you would want to consider the viewability of data when making those adjustments.

 

[vascobmcastro]

Hi JLGWhiz, thank you for your answer. However, maybe I wasn't clear in my question

I would like to adjust columns' widths based on if a cell is empty or not. Basically, if C2 is empty, then the column C width should automatically become 6 px. If any cell in row 2 is empty, those columns corresponding to those cells (D2 -> D, F2->F) should become 6px. However, the data is changing quickly, meaning that after I refresh the data, the columns can change and then they should all become the standard width of 25px unless these cells in row 2 are empty.

Any suggestion on how to solve this would be very helpful

Thank you!
Vasco

 

[Rick Rothstein]

However, the data is changing quickly, meaning that after I refresh the data,

How is the data changing (by formula or by manual entry)?

How are you refreshing the data (describe what you do)?

 

[Fluff]

Cross posted adjust column width based on empty cell within same column

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[JLGWhiz]

Hi JLGWhiz, thank you for your answer. However, maybe I wasn't clear in my question

I would like to adjust columns' widths based on if a cell is empty or not. Basically, if C2 is empty, then the column C width should automatically become 6 px. If any cell in row 2 is empty, those columns corresponding to those cells (D2 -> D, F2->F) should become 6px. However, the data is changing quickly, meaning that after I refresh the data, the columns can change and then they should all become the standard width of 25px unless these cells in row 2 are empty.

Any suggestion on how to solve this would be very helpful

Thank you!
Vasco

Why not just use Columns.Autofit after you refresh. That should take all empty columns to standard and the others will be sized to best fit for data contained. If you just base it on empty cells in a row, it could conflict with another row where the cells in that column are not empty. If you mean you only want that particular cell sized and not the entire column, then that won't work.

 

[vascobmcastro]

Hej Rick,

the update will be done just by refreshing pivottables but the data itself will be changed regularly. So, I'll create multiple reports based in a dynamic table.

Anyhow, imagine this; after updating the data, cell C2 becomes empty D2 has some text, E2 has text, F2 empty and G2 has text. Columns D, E, and G should then be adjusted to 25px width and columns C and F to 3px width.
Then, I adjust somethings and C2 now has text D2 has some text, E2 empty, F2 has text and G2 has text. Columns C, D, F, and G should now have 25px width and column E should now have 3px width.
And so on...

Does this make sense now?

@Fluff: I understand. The other post is removed right now but if it comes back I'll make sure to link this.

Thank you!

 

[Rick Rothstein]

You did not tell me if there were formula in the cells or not, so this is just a guess. Maybe these two lines of code do what you want (change the range reference as needed)...

Range("G3:AF3").ColumnWidth = 3
Range("G3:AF3").SpecialCells(xlConstants).ColumnWidth = 25

 

[vascobmcastro]

Thank you Rick!

Sorry, I didn't understand... Yes, those cells have formulas connected to those. Let me give it a try and let's see how that goes.

Vasco

 

[Rick Rothstein]

Sorry, I didn't understand... Yes, those cells have formulas connected to those. Let me give it a try and let's see how that goes.

Nope! My method cannot be modified to work with formulas in those cells unless those formulas all returned numbers. Since you said they return text, my approach will not work.