VB Code: Print area across hidden columns (where number of columns that are hidden changes weekly), but not to the end of the data

[AliM01]

I want to select/print columns on the right of and including “A,” across the hidden columns (which changes), to include 16 columns for printing in a relative manner.

Background:
Visual basic macro in Excel 2010 using Windows 8.
I have a workbook that has hidden columns (relating to previous weeks), and each week the number of columns that are hidden, change (by another user).
The first two columns contain text labels (A&B) and stay constant. So this week I see Columns A, B,| GH, GI, GJ etc, next week it will be A, B, | GI, GJ, GK etc.
I want to select columns on the right of and including “A” across the hidden rows, to include 16 columns for printing.
I tried recording in relative mode but that didn’t work. Ie. Shift + end + down, then shift+right arrow 16 x.
Data is continuous and goes beyond the area I want to print so using selection.End(xlright) selects too much data.
I am not the workbook owner so have created a macro in my personal workbook to use.
I am not a programmer and realise this can be tidied up (as select doesn’t need to be used) but I and am out of my depth here and would appreciate your help

Have searched the web and played with ActiveCell.Offset but I can’t get it to work due to the hidden columns changing weekly.
Here are some that I have tried.

Sub 1 – macro recorded in relative mode
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:GU144").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$GU$144"
ActiveCell.Select
End Sub

Sub 2 – using offset - I am using this macro at the moment.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(0, 204)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
End Sub

Sub 3 using ActiveCell
Range( _
ActiveCell.End(xlDown), _
ActiveCell.Offset(0, 204)).Select
ActiveCell.End(xlUp).Offset(1, 0),
ActiveSheet.PageSetup.PrintArea = Selection.Address
End Sub

Thanks

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Macro1()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    i = 16
    [color=darkblue]Do[/color] [color=darkblue]Until[/color] Range("A1").Resize(, i).SpecialCells(xlCellTypeVisible).Count = 16
        i = i + 1
    [color=darkblue]Loop[/color]
    ActiveSheet.PageSetup.PrintArea = Range("A1").Resize(ActiveSheet.UsedRange.Rows.Count, i).Address
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[AliM01]

Dear AlphaFrog.
Thank you very much for your response – Definitely on the right track as it prints the correct number of columns that I want.
However it prints several pages of blank rows, beneath what I require to be printed..
How do I incorporate the requirement to print only the rows with data. Previously I had;
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
… and that worked.

 

[AlphaFrog]

[color=darkblue]Sub[/color] Macro1()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    i = 16
    [color=darkblue]Do[/color] [color=darkblue]Until[/color] Range("A1").Resize(, i).SpecialCells(xlCellTypeVisible).Count = 16
        i = i + 1
    [color=darkblue]Loop[/color]
    ActiveSheet.PageSetup.PrintArea = Range("A1", Cells(Range("A" & Rows.Count).End(xlUp).Row, i)).Address
End [color=darkblue]Sub[/color]

 

[AliM01]

You beauty! This works perfectly,
Thank you very much for your help.