Cells Out Of Print Range 1

[HLEE1167]

Do you know if there is a way to determine if there are cells with any value that had not been included in the selected print range?


Thank you

 

[rmikesmith]

The following function will determine whether used cells exist outside of the print range:

Function CellsExistOutsidePrintArea(ByVal Wks As Worksheet) As Boolean
   With Wks
     CellsExistOutsidePrintArea = .UsedRange.Count > .Range(.PageSetup.PrintArea).Count
   End With
End Function

If you want to return those cells as a range the function below will do that but is probably not very efficient. On large used ranges it is likely to be slow.

Function GetCellsOutsidePrintArea(ByVal Wks As Worksheet) As Range
Dim PrintRng As Range
Dim OneCell As Range
Dim ResultRng As Range

   With Wks
     Set PrintRng = .Range(.PageSetup.PrintArea)
     For Each OneCell In .UsedRange
       If OneCell.Value <> "" Then
         If Application.Intersect(OneCell, PrintRng) Is Nothing Then
           If ResultRng Is Nothing Then
             Set ResultRng = OneCell
           Else
             Set ResultRng = Application.Union(ResultRng, OneCell)
           End If
         End If
       End If
     Next OneCell
   End With
   Set GetCellsOutsidePrintArea = ResultRng
   
End Function

HTH
Mike

 

[xlbo]

slightly different way:

Sub tester()
Dim UsedRng As Range, PrintRng As Range, iSect As Range

Set UsedRng = ActiveSheet.UsedRange
Set PrintRng = Range(ActiveSheet.PageSetup.PrintArea)

Set iSect = Intersect(UsedRng, PrintRng)

If iSect.Address <> UsedRng.Address Then
    MsgBox "Data outside of Print Area"
Else
End If

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Odd, I thought I posted to this yesterday ...

Anywho, it was much like xlbo's. That will be much faster than any loop.

-----------
Regards,
Zack Barresse

 

[rmikesmith]

Zack,

Not sure if the loop reference was to my code or not. If so, the function I posted to detect whether used cells exist outside the print range does not employ a loop; only the second function to return the actual range of cells outside the print range. That I acknowledge as likely to be rather slow, specifically because it loops through a range. I'm sure there are optimizations that could be made including, possibly, a completely different approach.


Regards,
Mike

 

[firefytr]

Hi Mike,

I just saw the loop and wanted to make the distinction that the more we can stay away from loops the better off (generally) our code will be. Sorry, definitely was not trying to take a dig at you; so please don't take it the wrong way.

Occasionally I just like to throw tidbits out there to help the user(s)/OP(s); I've found sometimes it's the little things that can make a difference for somebody.

-----------
Regards,
Zack Barresse

 

[rmikesmith]

Zack,

No offense taken. Just wanted it to be clear which part of the solution contained a loop (and, therefore, likely to be a performance bottleneck, as you pointed out). Besides, I do have some bad habits that are tough to eliminate.


Regards,
Mike

 

[HLEE1167]

Thank you for your answers. I did not had chance to answer before since I was on vacation but as always your answers are just impressive. I was able to solve the problem with this piece of code:

lc_lastarea = ActiveCell.SpecialCells(xlLastCell).address
lc_printarea = Right(oSheet.PageSetup.PrintArea, Len(oSheet.PageSetup.PrintArea) - InStr(1, oSheet.PageSetup.PrintArea, ":"))
If lc_printarea <> lc_lastarea Then


Please not that I am not an expert and this solution help me but I think that yours is more elegant and efficient