Excel Autofit Question 1

[jbl1167]

I do have many Excel Worksheets with columns that may contain hundreed of thousands of characters. I am using VBA to expand the columns using Autofit method to expand the columns and avoid the ###### replacement that Excel do in very populated columns. What do you sugest me to expand the columns and being able to see the content? Here is a sample of the code I am using. It should expand every single column in every spreadsheet of the workbook.

Cells.Select
With Selection
.Font.name = "Times New Roman"
.Font.Size = .Font.Size - 1
EntireRow.AutoFit
EntireColumn.AutoFit
End With


Thank you

 

[SkipVought]

Hi,

Its your object reference that is missing in the With structure

         With Cells
              .Font.name = "Times New Roman"
              .Font.Size = .Font.Size - 1
              [highlight yellow].[/highlight]EntireRow.AutoFit
              [highlight yellow].[/highlight]EntireColumn.AutoFit
         End With

Skip,

_[red][/red]

 

[jbl1167]

I am sorry because I am new at this Excel programming (forced by circumstances). Could you tell me how do I fix the problem?

Thank you

 

[SkipVought]

Simply correct your code.

Skip,

_[red][/red]

 

[jbl1167]

Thank you,

I was nervous and I did not understod the error but now I saw it and I will be able to correct it and try again.

 

[mrdod]

I'm certainly no expert but this may help. In your original post you are wanting to adjust the columns but you are referencing the rows also. Anyways here is how I would do it but I'm sure there are pros on here who have a better way to skin this cat.

Sub Try_This()

Dim n As Long
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets
sht.Activate
For n = 1 To Columns.Count
Columns.Select
With Selection
.Font.Name = "Times New Roman"
.Font.Size = .Font.Size - 1
.EntireColumn.AutoFit
End With

Next n
Next sht

End Sub

Good Luck

 

[SkipVought]

There is not need to loop thru 256 columns, or any columns for that matter, using the Cells Range Object.

There is no need to use the Select Method. Simply reference the range directly.

In its simplicity it is ...

SomeSheetObject.Cells.EntireColumn.AutoFit

Skip,

_[red][/red]

 

[mrdod]

LOL!! like I said there are pros on here who can skin this cat better than me.

 

[SkipVought]

could even be...

[b]SomeSheetThatsNotActiveObject[/b].Cells.EntireColumn.AutoFit

Skip,

_[red][/red]

 

[jbl1167]

Guys,

The program is expanding the columns but the problem now is that when you print there are columns that contain the multiple #### even when on the Excel environment they looking well. I tried changing the font to courier new (as Microsoft recomends and still is not working)