Here, we have declared sht as Worksheet, last_entry as Integer, and then, set it to the worksheet FIND. Last_entry = ("*", SearchOrder:=xlByRows, _ ➤ Write the following code Sub setting_printable_area_2() With the help of the VBA FIND function, we will try to set the print area for the following dataset. Method-2: Setting Print Area Dynamically Using VBA FIND Function Read More: Excel VBA: Set Print Area for Multiple Ranges (5 Examples) Here, in the Print Preview section, you can see the newly added product and its records in this print area. To prove the dynamicity, run the following code again by pressing F5. Now, suppose you want to add the sales records for the new product Potatoes in the dataset. Then, you will get the preview of our defined print area below. Finally, we have used ActiveSheet.PrintPreview for previewing the print area. Then, the print area is set to the named range Updated_Range and after that, using the WITH statement we have declared different features of the printing area. Here, we have declared sht as Worksheet and then, set it to the worksheet Named Range. BottomMargin = Application.InchesToPoints(1) TopMargin = Application.InchesToPoints(1) RightMargin = Application.InchesToPoints(1) LeftMargin = Application.InchesToPoints(1) ➤ Write the following code Sub setting_printable_area_1() Then, the Visual Basic Editor will open up. ➤ Go to the Developer Tab > Visual Basic Option. Then, you will be taken to the Name Manager dialog box where you will see the name of your created named range and its corresponding formula. OFFSET(‘Named Range’!$B$3,0,0,10,3) → OFFSET returns the range starting from cell $B$3 with a height of 10 and width of 3. COUNTA(‘Named Range’!$3:$3) → COUNTA will return the total number of non-blank cells in Row 3 as the Width.COUNTA(‘Named Range’!$B:$B) → COUNTA will return the total number of non-blank cells in Column B as the Height.➤Type the following formula in the Refers to box and press OK. Then, the Name Manager dialog box will appear.Īfter that, a new wizard New Name will open up. ➤ Go to the Formulas Tab > Defined Names Group > Name Manager Option. For this purpose firstly we will create a dynamic named range and then with the help of a VBA code, we will print that range easily. Here, we will try to set a print area that will contain the following records of the products, and any further records of the newly added products will be automatically included in this area. Method-1: Setting Print Area Using a Dynamic Named Range We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience. So, for obviating the need of setting up the print area every time after updating, we will use the following 7 different ways to set this area dynamically. Here, we have the following dataset containing the sales records of some of the products for different regions and we want to update this dataset with records of the new products. 7 Ways to Set Print Area Dynamically Using Excel VBA
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |