VBA code to only print until the last visible row.

[JoshTheTofu]

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:

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

 

[Trixterz]

Try using this...

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]

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]

How about this...

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]

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]

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]

Hi Fluff

Thanks for the help.

 

[JoshTheTofu]

Hi again (I am new to forums too haha)

The code is

=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]

Can you post some sample data using the XL2Bb add-in, showing the Data sheet, the Another sheet & the print sheet.