Solved: set print area

[wilg]

I have the code below which sets my print area of a sheet but does not work perfectly for me.

I have a formula which is iserror returns "" in column 26 or colums z.

If there is no formula there this code works fine.

If I have the formula in column z then this code finds the last instance of "" and sets the print area to that.

I only want the print area to be set to where there is actual data in the cell not "".




Sub setprintarea()
Dim myrange As String
myrange = Cells(Rows.Count, 26).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$A$2:" & myrange End Sub

[GTO]

Do you mean that there are literally a pair of double quotes left, or just an empty string (empty cell)? If the cell appears empty, but .End(xlUp) is stopping at it, I would use the Find method to locate the last cell with a value.

[wilg]

I mean literally "" as my formula is if(iserror(formula),"")

what is the find method?

[Aussiebear]

Where is this formula located? The last row or rows perhaps?

[GTO]

Not exactly pretty, but I think that this would work.


Option Explicit

Sub SetPrint()
Dim PrintAddress As String

'// Get the address of the range we are looking at. Below presumes a header row, //
'// change to suit //
PrintAddress = Range(Sheet1.Range("Z2"), _
Sheet1.Cells(Sheet1.Rows.Count, "Z")).Address(False, False)
'// Pass the sheet and address. If SetPrintRange returns True, 'PrintAddress' will //
'// come back with with the address we want. //
If SetPrintRange(Sheet1, PrintAddress) Then
Sheet1.PageSetup.PrintArea = PrintAddress
Else
Sheet1.PageSetup.PrintArea = ""
End If
End Sub

Function SetPrintRange(ByVal Sh As Worksheet, ByRef RangeAddress As String) As Boolean
Dim rngFound As Range
Dim i As Long

With Sh
'// Find the last cell with anything, including quotes. to narrow down the //
'// remaining searching. //
Set rngFound = RangeFound(.Range(RangeAddress))
If rngFound Is Nothing Then
SetPrintRange = False
Exit Function
End If

'// If we made it here, reset our range to look in, from the first cell in the //
'// original range to the last cell we found anything in. //
Set rngFound = Range(.Range(RangeAddress).Cells(1), rngFound)
End With

'// Since we are only looking in a one-column range, we can just use the Index of //
'// .Cells. //
For i = rngFound.Cells.Count To 1 Step -1
'// I wasn't sure so looked for either one or two double-quote marks, or an //
'// empty cell; change to suit //
If Not (rngFound.Cells(i).Text = """" _
Or rngFound.Cells(i).Text = """""" _
Or rngFound.Cells(i).Text = vbNullString) Then

'// Once we find a cell that's not empty or just has quotes, grab the //
'// the address of the resized range and jump out. //
RangeAddress = rngFound.Resize(i).Address(False, False)
SetPrintRange = True
Exit For
End If
Next
End Function

Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

You asked about the Find Method, it is used in the last function. You can type in 'Find Method' in vba help and see the various args/options.

Hope that helps,

Mark

[wilg]

Hi guys, been trying to work with your suggestion but I cannot get this to work 100%. Maybe I can give more info.

I need to set print area from columns A to Z.

Starting at row 2

Row 2 and 3 are headers or titles.

Row 4 up to 500 will have data.

Column A has formula =IF(ISERROR(VLOOKUP(B395,'2 STATUS'!A393:C891,3,FALSE)),"",VLOOKUP(B395,'2 STATUS'!A393:C891,3,FALSE))

Column Z has a formula =IF(ISERROR(VLOOKUP($B395,INDIRECT("'"&INDEX(mysheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mysheets&"'!A119:AU119"),$B395)>0),0))&"'!A119:aU11920"),47,0)),0,VLOOKUP($B395,INDIRECT("'"&INDEX(mysheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mysheets&"'!A119:AU119"),$B395)>0),0))&"'!A119:aU11920"),47,0))

I would like to set the print area to stop at the last row which returns result other than "" which is the iserror result.

Is there a way to manipulate a set print area to if not "" then include in print area?

[wilg]

This works if there is no formula in column z.
Sub SetArea()
ActiveSheet.PageSetup.PrintArea = Range("A2", Range("z65536").End(xlUp)).Address
End Sub

but if I have this formula
=IF(ISERROR(VLOOKUP($B486,INDIRECT("'"&INDEX(mysheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mysheets&"'!A119:AU119"),$B486)>0),0))&"'!A119:aU11920"),47,0)),0,VLOOKUP($B486,INDIRECT("'"&INDEX(mysheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mysheets&"'!A119:AU119"),$B486)>0),0))&"'!A119:aU11920"),47,0))

It sets the print area to my last row which is 500. I need the page set up to be at which ever last row has something other than the iserror result ""

[pjotter]

This works if there is no formula in column z.
Sub SetArea()
ActiveSheet.PageSetup.PrintArea = Range("A2", Range("z65536").End(xlUp)).Address
End Sub

but if I have this formula
=IF(ISERROR(VLOOKUP($B486,INDIRECT("'"&INDEX(mysheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mysheets&"'!A119:AU119"),$B486)>0),0))&"'!A119:aU11920"),47,0)),0,VLOOKUP($B486,INDIRECT("'"&INDEX(mysheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mysheets&"'!A119:AU119"),$B486)>0),0))&"'!A119:aU11920"),47,0))

It sets the print area to my last row which is 500. I need the page set up to be at which ever last row has something other than the iserror result ""

Just use the
Sub SetArea()
ActiveSheet.PageSetup.PrintArea = Range("A2", Range("z65536").End(xlUp)).Address
End Sub

to check what cell is the last cell to contain the formula... then from there loop up to check whether the cell is containing "" or an actual value.

Would become like this:


Sub SetArea()
Dim LastCellRow
LastCellRow = ActiveSheet.Range("Z65536").End(xlUp).Row
While Cells(LastCellRow, 26) = ""
LastCellRow = LastCellRow - 1
Wend
ActiveSheet.PageSetup.PrintArea = Range("A2", Cells(LastCellRow, 26)).Address
End Sub


That does the trick for me.

[wilg]

That's it pjotter. It worked. Good job. Thanks guys for all your assistance. It's very much appreciated.

Mike.