Cells.EntireColumn.AutoFit [repost in English]

user_19839754 0
Viewed 78

I edited original question, but due to three votes to delete (in one hour after inserting) no-body mentioned it (in folloeing two days...), I'm sorry I have to re-post it as new question. Original question https://stackoverflow.com/questions/73476875/vba-excel-cells-entirecolumn-autofit

First of all - thanks cybernetic.nomad he used Google Translator to translate my question. I didnt't know I have to write in English - first I asked on Microsoft (in Czech :-) ) and they only replied that I should give the same question here. Unfortunately they do not mentioned I have to translate to English... But do not thanks to other three members who immendiatelly deleted the question. I think it should be fair to give me some time to confirm/upgrade translation....

Now again translated question:

Hi, I have a small problem...

I have an Excel table, about 100 rows and 170 columns.

If I use the whole area markup and adjust the width of the content columns (using the double arrow between the column header markup), it takes Excel about 3 seconds (to auto-adjust all columns).

But when I do it with the macro (VBA) through Cells.EntireColumn.AutoFit function, then the whole operation (auto-adjust all columns) takes a few minutes! Does anyone have an idea how to do this action in the macro faster?

In addition, I subsequently hide some columns using Columns(i).EntireColumn.Hidden = True, but the time difference is not that important there.

Regarding cybernetic.nomad's reply - no, I'm not using Columns(i).EntireColumn.Hidden = True inside a loop - bellow is staded complete VBA code I'm using. When making it manually, I selected the square on left top (on crossing of rows and columns names) and then double-clicked double-arrow.

Sub Hromadne()
    Application.ScreenUpdating = False
    Selection.AutoFilter Field:=1, Criteria1:="<>0"
    RadekDatSkryti = Sheets("Hromadné").Range("C3").Value
    Rows(RadekDatSkryti).EntireRow.Hidden = True
    Cells.EntireColumn.AutoFit
    
    For i = 3 To 167
    If Cells(RadekDatSkryti, i).Value = 0 Then
    Columns(i).EntireColumn.Hidden = True
    Else
    'Columns(i).EntireColumn.Hidden = False
    End If
    Next i
    
    Application.ScreenUpdating = True
End Sub
vba excel
1 Answers

Try manual calculation mode. Speeds up my macro x10

Sub Hromadne()
Application.ScreenUpdating = False
        
'turn off automatic calculation'
Application.Calculation = xlCalculationManual
        
'Manual Calculate if formulas need to updated when automatic calculation is off
' - insert where needed
'ThisWorkbook.Worksheets("Hromadné").Calculate

Selection.AutoFilter Field:=1, Criteria1:="<>0"
RadekDatSkryti = Sheets("Hromadné").Range("C3").Value
Rows(RadekDatSkryti).EntireRow.Hidden = True
Cells.EntireColumn.AutoFit
        
For i = 3 To 167
    If Cells(RadekDatSkryti, i).Value = 0 Then
        Columns(i).EntireColumn.Hidden = True
    Else
        'Columns(i).EntireColumn.Hidden = False
    End If
Next i
        
Application.ScreenUpdating = True
    
'turn on automactic calculation'
Application.Calculation = xlCalculationAutomatic
    
End Sub
user_198557810
Related