How to autofit all columns in excel but ignore the hidden columns C#

Question

I need to autofit the columns in a chart in excel and am using the following code to do so

//img.cellRange = "G6:Q39" for example
workbook.ActiveWorksheet.Cells[img.cellRange].Columns.AutoFit();

This works well but there are some columns in my chart (within the range specified above) that are hidden as they contain information I dont want to see. Columns.AutoFit() ends up autofitting all the columns including the hidden ones.

How can I make it so that the code ignores the hidden columns and only autofits those columns that are not hidden? Also the range will vary, it will not always remain the same as this is being used for several different sheets.

Answer 1

You could iterate over each column in your range with IRange.Columns and only auto-fit if that column is visible (IRange.Hidden). Example:

// Get the range that should be auto-fitted.
IRange range = workbook.ActiveWorksheet.Cells[img.cellRange];

// Iterate over your range in column "chunks", e.g., for your example range
// of G6:Q39 it would be split and loop over G6:G39, H6:H39, I6:I39, etc.
foreach (IRange column in range.Columns)
{
    // Only auto-fit if the column is visible.
    if (!column.Hidden)
    {
        column.AutoFit();
    }
}