Excel VBA - printing problem

[Corbie]

Could anyone please help me with this simple printing
problem which is driving me crazy.

I'm reporting on a number of cost centres with each cost centre having 4 columns. I want to print six costs centres
side by side each page. Fine , loop through and at each 4x6=24 column print a page. The problem I have is that some cost contres are not applicable and have been hidden by another module. I know I need to loop through testing if the columns are hidden and then print at every sixth non hidden set, but can't seem to get it right.

Any help would be appreciated

 

[Loomah]

Hi Corbie
This should set you on your way...

Sub cols()
Dim iCol As Integer
Dim iCnt As Integer
iCol = 1
iCnt = 0
MsgBox ActiveSheet.UsedRange.Columns.Count '42
Do
    If Not Columns(iCol).Hidden Then
        'MsgBox iCol 'test
        iCol = iCol + 4
    iCnt = iCnt + 1
    Else
        iCol = iCol + 1 'may be changed if hidden columns ALWAYS = 4
    End If
Loop While iCol <= ActiveSheet.UsedRange.Columns.Count And iCnt < 6
    Range(Columns(1), Columns(iCol - 1)).Select ' or do whatever!
End Sub

This checks each column and if it isn't hidden 'notes' the column together with the next 3 (4 in total.) It continues to do this until it has done so 6 times then selects all the relevant visible columns. If a column is hidden it moves to the next.

Hope this helps
;-)



If a man says something and there are no women there to hear him, is he still wrong?

 

[Corbie]

Thanks, I get the idea now. I was going round in circles for hours, should be ok now.