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

  • Thread starter Thread starter Mel
  • Start date Start date
M

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
 
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
 
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
Click to expand...
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
Click to expand...
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
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:
Click to expand...
     ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(LastRow, LastColumn - 1)).Address
Click to expand...
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.
Click to expand...
Another way would be to hide column I before the printout, and then
unhide it after.
Click to expand...
Hope this helps.
Pete
Click to expand...

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 -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...
 
Back
Top