The Cells(row, column) property counts hidden columns 1

[cardiac]

I'm writing a workload planner for all the departments at my office. Each dept. is represented by a column. As there are several admin staff, each responsible for a different group of depts., I hide the irrelevant columns (depts.) depending on which admin member has opened the file.

The problem occurs when I use a loop to iterate through all the visible columns and check whether the cell contains a 'Y' or a 'N' (whether the latest piece of work affects this dept. or not). I use a loop like the following:

  For i = 1 to intNumberOfDepts
            'intRow & intCol are defined elsewhere
            strAffected = sheet1.Cells(intRow, intCol + i)
            If strAffected = "Y" Then . . . 
            ElseIf strAffected = "N" Then . . .
            Else  . . .
            End If
        Next i

I need the loop to ignore the hidden columns and just process the visible ones, but the Cells property counts them all!

Is there a way 'round this?

Many thanks,

David

 

[GlennUK]

Look at the SpecialCells(xlCellTypeVisible) method.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.