Insert Pagebreaks - SpecialCells(xlCellTypeVisible) VPageBreaks.Add

[PWD]

Good afternoon. I'm sure the syntax is straightforward but at the end of the week I'm a bit stumped.

I can determine the number of visible (not hidden) columns

MyNumber = .Range("A1").Resize(1, EndCol).SpecialCells(xlCellTypeVisible).Count

but I'm just having a bit of trouble putting page breaks in realtive to the visible columns only.

.VPageBreaks.Add Cells(1, y + 4)

This only puts page breaks in for the absolute column numbers. Any ideas?

Many thanks,
D€$

 

[PHV]

What is your actual code (looping with y ?)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

Hidden rows/columns makes hard page breaks kind of dicy to figure.

What is the problem with the soft breaks as they exist?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi guys. Basically we (my colleague) have columns we need to keep together for output, e.g. 1 x yellow followed by 2 x white. Depending on criteria he may delete blocks of 3 columns. He will also have columns A & B repeated on each page. So, for the sake of argument, Page 1 will be columns A, B, C, D, E, F, G & H; Page 2 might be A, B, L, M, N, R, S, & T. I thought of putting 'y' in the loop which I would increment each time. This code was just to test & see what would happen:-

    With ActiveSheet
y = 1
EndCol = .Range("A1").End(xlToRight).Column
MyNumber = .Range("A1").Resize(1, EndCol).SpecialCells(xlCellTypeVisible).Count
TotalCols = MyNumber - 2

TotalTimes = Int(TotalCols / 4)

For x = 1 To TotalTimes

.VPageBreaks.Add Cells(1, y + 4)

y = y + 4

Next x

    End With

I would also need a method for 'removing' existing page breaks. The only code I could find was for resetting:-

.ResetAllPageBreaks

Many thanks,
D€$

 

[PWD]

Well done me!! I've tested this using just 4 columns (to make it easier to see) & it appears to do what we need.

Sub Des()
v = 0
With ActiveSheet
EndCol = .Range("A1").End(xlToRight).Column
    For x = 3 To EndCol
        If .Cells(1, x).EntireColumn.Hidden = False Then
        v = v + 1
            If v = 4 Then
            .VPageBreaks.Add Cells(1, x + 1)
            v = 0
            End If
        End If
    Next x
End With
End Sub

I know I'm rubbish with dimensioning - that'll be my next self-improvement target!!

Many thanks,
D€$