How to determine if there are hidden columns when copying in Excel VBA

Question

As the title explains, I have an Excel 2003 workbook and I'm copying a number of columns of one sheet to another in VBA. Unknown to me, someone has hidden a few columns on the source sheet and it has messed up how I process the cells in the destination sheet.

How can I programmically determine:

1. IF there are hidden columns
2. WHICH columns are hidden?

Thanks! JFV

Answer 1

For a Range, check the Range.Hidden property.

The following snippet from MSDN is a good example of how to hide/unhide a row/column:

 Worksheets("Sheet1").Columns("C").Hidden = True

You can also test the value with an If statement:

 For ColCounter = 1 To 10
      If Columns("C").Hidden = True Then
           Columns("C").Hidden = False
      End If
 Next

Answer 2

you can write a function like:

Function IsColumnHidden(column As Variant)

if Columns(column).ColumnWidth = 0.0 then

IsColumnHidden= true

else IsColumnHidden= false

end if

End Function

column width or row height of 0.0 is an indicator if a row or column is hidden.

Answer 3

If you only want to copy the visible files then one option that is quite nice it to select only the visible columns as a range.

This can be done by

Set visrng =rng.SpecialCells(xlCellTypeVisible)

It wasn't clear to me if this would be useful in your case, but I decided to post it as it could be useful to others.