How to decrypt multiple columns?

Excelvba - selection of a dynamic range with hidden columns

• QUESTION: I am building up a database inside an2007 worksheet and that creates an huge table with a dynamic, and always increasing, numer of rows. I created two macros, through which I change the numer of columns shown in the worksheet, one is the total one, while the other one shows less columns, so it's a more synthetic view. Now the problem. I am trying to build up a macro, in the more synthetic case, that selects the Print Area (naturally represented only by the non-empty cells) and sets up all the other options. Since the synthetic view is created by hiding some columns, is there a way to select such a dynamic Range without catching the hidden columns too? As you may see from the code I am posting, I refer to a "defined Range" just as an example, since I don't know how to select a dynamic range with hidden columns inside, that shall not be considered from the selection. This piece of information is crucial for me because it will help me with creating other procedures for subtotals, filters and so on. With the undermentioned code I also try to directly print the selected area, after having set up all the options. Thanking you, I am looking forward to receing your kind help. This is the code Sub Stampa() Application.ScreenUpdating = False Range("B2:AF6").Select ActiveSheet.PageSetup.PrintArea = "$B$2:$AF$6" With ActiveSheet.PageSetup .PrintTitleRows = "$2:$3" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$2:$AF$6" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 10 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)" Application.ScreenUpdating = True End Sub ANSWER: If the columns are hidden, then including them in the print area is fins, since they won't print, because they're hidden! All you need is this (not the recorded monstrosity): Sub Stampa() Range("$B$2:$AF$6").Name="Print_Area" Activesheet.Printout End Sub ---------- FOLLOW-UP ---------- QUESTION: The recorded "monstrosity", as you you just defined the code I posted, contains all the options I need for the database report to be printed. They are options I need to be applied, thus they have to be set up with the code. Said that, this "monstrosity" perfectly works out, while the code you posted doesn't work out at all, since it prints out with default settings and just the headers of the table. Finally, if you read more carefully my query, I kindly asked to know the procedure to select A DINAMIC RANGE OF CELLS, specifying that the number of rows IS ALWAYS INCREASING. I hope it is clearer now. Waiting for your feedback.

• Answer:

  OK. First, 80% of the info recorded is really not necessary. Perhaps the necessary ones are .FitToPagesTall, .Zoom, .FitToPagesWide, and a few others. That being said, you can add this one line after the End With, assuming the rows increase down column B as well: Range("B2:AF" & Range("B65536").End(xlup).Row).Name="Print_Area"