ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Set column width to fixed size if there's no data in it at all(except heading in row1) (https://www.excelbanter.com/excel-programming/454406-set-column-width-fixed-size-if-theres-no-data-all-except-heading-row1.html)

JS SL

Set column width to fixed size if there's no data in it at all(except heading in row1)
 
Hello,

Simple question, but...€¦ what's the answer :)
Somebody knows it ? Thanks !

In a sheet there are many columns, but a few of them hasn't registered data in the rows. When using Cells.EntireColumn.AutoFit it's allways on a size that could be a bit smaller.

Therefore I like to run a macro that runs from first column to last used and check in each column if there is data (in row 2 till last used row). If not, then set the column width on 10.


Regards, Johan

Claus Busch

Set column width to fixed size if there's no data in it at all (except heading in row1)
 
Hi Johan,

Am Mon, 19 Aug 2019 09:44:08 -0700 (PDT) schrieb JS SL:

In a sheet there are many columns, but a few of them hasn't registered data in the rows. When using Cells.EntireColumn.AutoFit it's allways on a size that could be a bit smaller.

Therefore I like to run a macro that runs from first column to last used and check in each column if there is data (in row 2 till last used row). If not, then set the column width on 10.


try:

Sub Test()
Dim myCol As Range

With ActiveSheet
For Each myCol In .UsedRange.Columns
If Application.CountA(myCol) <= 1 Then
myCol.ColumnWidth = 10
Else
myCol.EntireColumn.AutoFit
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

JS SL

Set column width to fixed size if there's no data in it at all(except heading in row1)
 
Thanks a lot (as usual).
Works as required.
regards, Johan


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com