how to print only rows with data without inlcuding rows with a formula

[Mel]

I have the fololowing macro set up to print only rows with a data.
Columns A to I down to row 2000. Column i has the formula only. I
have no idea how to exclude the formula so that it does not count as
data. thx Mel


Sub SetUsedPrintArea()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
' MsgBox LastColumn
End If
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn)).Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

 

[Pete_UK]

If you can guarantee that there is no data to the right of column I,
then you could change your penultimate line to this:

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn - 1)).Address

Note that this line has actually wrapped onto a second line (when
viewed in GG anyway), but it should be one continuous line in your
editor.

Another way would be to hide column I before the printout, and then
unhide it after.

Hope this helps.

Pete

 

[Mel]

If you can guarantee that there is no data to the right of column I,
then you could change your penultimate line to this:

     ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn - 1)).Address

Note that this line has actually wrapped onto a second line (when
viewed in GG anyway), but it should be one continuous line in your
editor.

Another way would be to hide column I before the printout, and then
unhide it after.

Hope this helps.

Pete

I have the fololowing macro set up to print only rows with a data.
Columns A to I down to row 2000.  Column i has the formula only.  I
have no idea how to exclude the formula so that it does not count as
data.  thx Mel

Sub SetUsedPrintArea()
    Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
              'MsgBox LastRow
    End If
    Dim LastColumn As Integer
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _
                           SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column
                 '  MsgBox LastColumn
    End If
    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn)).Address
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub- Hide quoted text -

- Show quoted text -

I tried that, no luck. Column's J to the end or hidden. There is no
data or anything to the right of column I.
column's J to the end are presently hiddent. I tried both hidden and
unhidden but no luck.

Currently I have about 8 rows (1 page) but when I select print I get
22 pages. Not sure why that is happening.
thx

Mel

 

[Pete_UK]

Debra Dalgleish has some notes here:

http://www.contextures.com/xlfaqApp.html#Unused

on why Excel often thinks the used range is larger than it really is,
and explains how you can adjust for this.

Hope this helps.

Pete

If you can guarantee that there is no data to the right of column I,
then you could change your penultimate line to this:

     ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn - 1)).Address

Note that this line has actually wrapped onto a second line (when
viewed in GG anyway), but it should be one continuous line in your
editor.

Another way would be to hide column I before the printout, and then
unhide it after.

Hope this helps.

Pete

I have the fololowing macro set up to print only rows with a data.
Columns A to I down to row 2000.  Column i has the formula only.  I
have no idea how to exclude the formula so that it does not count as
data.  thx Mel
Sub SetUsedPrintArea()
    Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows..
        LastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
              'MsgBox LastRow
    End If
    Dim LastColumn As Integer
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1],_
                           SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column
                 '  MsgBox LastColumn
    End If
    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn)).Address
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub- Hide quoted text -

- Show quoted text -

I tried that, no luck.  Column's J to the end or hidden.  There is no
data or anything to the right of column I.
column's J to the end are presently hiddent.  I tried both hidden and
unhidden but no luck.

Currently I have about 8 rows (1 page) but when I select print I get
22 pages.   Not sure why that is happening.
thx

Mel- Hide quoted text -

- Show quoted text -