Set print range for all cell containing data but ignoring formulas

[Gingermuppet]

Set print range for all cell containing data but ignoring formulas

I have a simple test formula to get get the range of cells with data in them. Works fine as a test. The problem is that my sheet contains formulas and excel treats them as data.

I can’t find a way of ignoring them they are simple formulas. i.e A1 =Sheet2!B4. it’s a blank cell to look as as that cell in sheet2 is blank and I don’t want sheet1 printing it and wasting paper.

This is the test macro I am using.

   Sub GetRange()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [a1], , , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column))
        MsgBox "Range is " & rng3.Address(0, 0)
        'if you need to actual select the range (which is rare in VBA)
        Application.Goto rng3
    Else
        MsgBox "sheet is blank", vbCritical
    End If
End Sub

Thanks in advance for help with my first post.

[Gingermuppet]

Solved it my self

Sub PickedActualUsedRange()
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

thanks for my help... its fine thanks. I know, no thanks really...