Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 19th 19, 05:44 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 30
Default 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

  #2   Report Post  
Old August 19th 19, 07:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
  #3   Report Post  
Old August 20th 19, 05:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 30
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fixed Column Width Export [email protected] Excel Discussion (Misc queries) 1 March 27th 08 01:27 PM
Column fixed width juanpablo Excel Discussion (Misc queries) 1 January 16th 08 12:57 PM
Text to column, fixed width Daniel Charts and Charting in Excel 2 August 15th 07 04:38 PM
Text to column, fixed width Daniel Excel Discussion (Misc queries) 0 July 9th 07 07:52 PM
Help: How do I create a column of fixed width data? limshady411[_11_] Excel Programming 1 December 14th 05 11:10 PM


All times are GMT +1. The time now is 02:41 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017