VBA to assign print area dynamically

[cubefarmed]

Greetings coders! I have a problem. My workbook contains two sheets appropriately named Data Dump and Formatting.

Within the Data Dump, I want to establish the amount of rows by using CountA("A3:A50"). For the sake of this question lets say there are 5 data rows.

I want to set my print area on Formatting to be B1 to H165. Columns B and C must be part of the print area, but the column disposition begins with the D column.

Example: If I have 10 rows in Data Dump, I want the print area to be B1:M165. Each additional row increases the Column of my print area by 1.

I hope this makes sense. I tried to conceptualize this in terms of variables and declarations I just started confusing myself. I'm a poor poor VBA student

Thanks for any help!

 

[p45cal]

lastColmNo = Application.WorksheetFunction.CountA(Sheets("DataDump").Range("A3:A50")) + 3
Sheets("Formatting").PageSetup.PrintArea = Range(Cells(1, 2), Cells(165, lastColmNo)).Address

?

 

[AlphaFrog]

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>    i = Application.WorksheetFunction.CountA(Sheets("Data Dump").Range("A3:A50"))<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Formatting")<br>        .PageSetup.PrintArea = .Range("B1:M165").Resize(, 2 + i).Address<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

 

[cubefarmed]

You both are amazing! Thank you so much!!

I used both of them and they both work. VBA is amazing. I need to work harder at learning it.

Truly appreciate the help. Have a great weekend.