Setting a print area in vba

**harveywalker** · 10-23-2012, 07:28 AM

Hi.

Hope someone can help me with a problem?

I would like to write a VBA print button which selects all entered data from Column A1 to ED (Static), and the last Row with "1" in Column ED (the last row starts at ED15, and goes all the way down to ED515, and depending on the number of items I'm pricing for, can either be A1 to ED15, or A1 to ED515)

At present in ED I've got a formula which is =IF(ISTEXT(A15),1,0)), but when I use xlUp, it selects the last row with the formula, not the "1", or "0".

Is it possible to use VBA to "ignore" the formula, or instead of 1, can I use " "?

I'm sure the answer is simple, but I'm not an expert in VBA.

Thanks!

**Chippy** · 10-23-2012, 09:16 AM

Use the VBA Find method, like this:

Sub Find_Print_Area()

    Dim EDcell As Range
    Dim printCells As Range
    
    Set EDcell = Range("ED15:ED515").Find(What:="1", After:=Range("ED515"), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
    If Not EDcell Is Nothing Then
        Set printCells = Range("A1", EDcell)
        MsgBox "Last 1 in column ED: " & EDcell.Address & vbCrLf & "Print area range: " & printCells.Address
    Else
        MsgBox "1 not found in column ED"
    End If
    
End Sub

I used the Macro Recorder to generate the Find call and edited the code. The printCells range gives you the print area range, which you could use as an argument to macro recorder-generated code whilst setting the print area.

**harveywalker** · 10-23-2012, 10:47 AM

Hi Chippy,

Thanks for getting back to me!

I'm just tying your suggestion out on my spreadsheet, so far it works fine (I've just got to get it to set the Print Area!)

I really appreciate the help.