AutoFit Rows according to VISIBLE COLUMNS

Hello guys

I've a data Base which i make some hidden columns to PRINT PDF, although i want now to autofit ROWS according to Columns Visible

I found this on the internet, although i cannot make this to work

My first data is on A9 (row and column)
Sheet name is "BD CLIENTES"

Sub AFitCols()    '*
    '** Autofit starting at row 9 (IQC is the WorkSheet name).
    '*
    Dim MaxRows As Long
    Dim MyRng As Variant
    Dim A As Range, Cel As Range
    
    ActiveWorkbook.Sheets("BD CLIENTES").Activate
    MaxRows = ActiveSheet.UsedRange.Rows.Count - 9
    '*
    '** We want colums A  to AA, so in the Cells we specify 1 to 27.
    '*
    Set MyRng = Range(Cells(1, 1), Cells(MaxRows, 27)).Offset(9).SpecialCells(xlCellTypeVisible)
    MyRng.Rows.AutoFit
    '*
    '** Increase row widths by set amount
    '*
    For Each Cel In MyRng.Columns(1).Cells
        Cel.RowWidth = Cel.RowWidth + 2
    Next
    Range("A9").Select
    
End Sub 'AFitCols

what exactly is NOT working? that's all you said. and to me, this doesn't make a lot of sense the way it is written:

although i want now to autofit ROWS according to Columns Visible

Originally Posted by vba_php

what exactly is NOT working? that's all you said. and to me, this doesn't make a lot of sense the way it is written:

Hi,

It is does not autofit according to visible columns.

Imagine you have text:
COLUNN A:
"XPTO"

COLUNN B:
"XPTO
XPTO
XPTO"

COLUNN C:
"XPTO
XPTO"

If i hide column B It should autofit rows according to text on C column instead of hidden B

Hello guys

Can anyone help me out?

why would one be able to fit rows to AUTOFIT based on what columns are visible and not visible? that makes no sense, right? a column is not a row. and hiding columns has nothing to do with autofitting rows so multi-line cells can reveal all of their text in terms of visibility.
i know you found that code on the internet, but did you try recording your actions with the macro recorder and just using that?

Ofc i did by myself but doesn't work when recording a macro

You can check file attached. Even with Column B hiden, when autofitting it consideres column B height

oh, now I get it! I don't think you can do it. and the reason => a row is a row and a column is a column. rows and columns intersect, and rows have every column of data associated with them regardless of whether they are hidden or not. and I'm not an excel pro, but it sounds right to me. what about workarounds?

first of all, why do you even have data like this? surely there is a better way to store it, right? the other thing you could obviously do as a workaround, would be to store the hidden col data in another sheet, print the PDF-based sheet, then cut the external data and paste it in back where it was before the printing. that would obviously work.

Yes must probably it would work to copy visible columns and rows to another sheet and then autofit

But how can i do that? xD

you do it like this, mr. beginner: ()

Function try_try_try_until_stuck()
Dim r As Range

    For Each r In Range("a1", Range("a1").End(xlToRight))
        If r.EntireColumn.Hidden Then
            r.EntireColumn.Select
            Selection.Cut
            Sheets("target_sheet_name_here").Select
            ActiveSheet.Range("a1").Select
            ActiveSheet.Paste
            ActiveSheet.Range("a:a").EntireColumn.Hidden = False
        End If
    Next r
End Function

almost all of that was taken by the recorder. now, I started it. you can finish it.

Got it, have a great day

cool! l..................