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 |
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 |
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