VBA code to only print until the last visible row.

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi guys

I have recently started a project on Excel and have hit a road block. I have learnt so much over the last few weeks although this is something that I have tried with but haven't found a solution. So I have come to you for some help.

I have a VBA macro which runs through a list of headers and prints each sheet out with the corresponding names. Here is the code:

VBA Code:
Sub Printout()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    Set xRg = Worksheets("Print sheet").Range("A5")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)
    For Each xCell In xRgVList
        xRg = xCell.Value
        If Not Application.WorksheetFunction.IsError(Range("B13")) = True Then
        Worksheets("Print sheet").PrintOut
        End If
       
    Next
End Sub

I have set the list to a maximum of 100 rows and used an =IFERROR(x,"") to hide all the erros when it runs out of names to find. The problem here is it prints the "blank" pages too. Is there a piece of code to add to the VBA to improve it and only print upto the last visible row? Or alternatively is there a better way to display the data without having to"fill" the 100 rows?

Also I am really keen on understanding the answer to this as I have really enjoyed learning about excel recently and can see myself doing more in the future.

Kind regards

Josh
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
Try using this...

VBA Code:
Sub Printout()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    
    Set xRg = Worksheets("Print sheet").Range("A5")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)

    For Each xCell In xRgVList
        xRg = xCell.Value

        If Not Application.WorksheetFunction.IsError(Range("B13")) = True Then
            ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
            Worksheets("Print sheet").Printout
        End If
    Next
End Sub
 

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Trixterz

Thank you for you suggestion. I have tried it but unfortunately it still counts the "empty" error cells as within the UsedRange so still prints 2 pages.

Kind regards

Josh
 

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
How about this...

VBA Code:
Sub Printout()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
   
    Set xRg = Worksheets("Print sheet").Range("A5")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)

    For Each xCell In xRgVList
        xRg = xCell.Value

        If Not Application.WorksheetFunction.IsError(Range("B13")) = True Then
            ActiveSheet.PageSetup.PrintArea = Split(ActiveSheet.UsedRange.Address, ":")(0) & ":$" & Split(ActiveSheet.UsedRange.Address, "$")(3) & "$" & XCell.Row
            Worksheets("Print sheet").Printout
            Exit For
        End If
    Next
End Sub
 

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi again

This will now only print the first two lines of the first sheet and then ends.

This is a really tough conundrum.

Kind regards

Josh
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Or alternatively is there a better way to display the data without having to"fill" the 100 rows?
What is the formula you have in the Print Sheet, to display the values according to A5?
 

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff

Thanks for the help.
 

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi again (I am new to forums too haha)

The code is

Excel Formula:
=IFERROR(INDEX(INDEX('Another sheet'!$C$5:INDEX('Another sheet'!$C:$C,Data!$B$1),0,1),SMALL(INDEX(Data!$C$5:INDEX(Data!$C:$C,Data!$B$1),0,1),ROW(1:1))-4,1),"")

A5 changes a list in a worksheet that I called data (I originally used offset on every line which destroyed the sheet), I now make it do the calculation once and then call it back to this sheet using the SMALL function to list the date and ROW(1:1) just to increment it upto ROW(100:100) as I know that I wont need any more than that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data using the XL2Bb add-in, showing the Data sheet, the Another sheet & the print sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,377
Messages
5,601,264
Members
414,439
Latest member
norideen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top