How to remove rows for printing

A

abdulie

I'm trying to put together a spreadsheet for ordering. There are approx
600 products and I would like to be able to only print the rows which
have data entered in the Quantity column. Does anyone have any clues as
to how I can do this?

I appreciate any ideas!

Cheers :confused:
 
S

ScottO

Have you tried the Auto Filter feature?
Look in Data/AutoFilter - it's self-explanatory, but refer to
in-built help if required.
Rgds,
ScottO

"abdulie" <[email protected]>
wrote in message
|
| I'm trying to put together a spreadsheet for ordering. There are
approx
| 600 products and I would like to be able to only print the rows
which
| have data entered in the Quantity column. Does anyone have any
clues as
| to how I can do this?
|
| I appreciate any ideas!
|
| Cheers :confused:
|
|
| --
| abdulie
| -------------------------------------------------------------------
-----
| abdulie's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30538
| View this thread:
http://www.excelforum.com/showthread.php?threadid=501930
|
 
A

abdulie

Autofilter doesn't work because there's headings in the way. I really
need something that would look up ranges of cells and delete any that
have not got a numeric value.

I think I'll have to do it in a macro or code to complete both steps???
 
R

Reg

Use >Data>Filter and create a criteria that extracts the non-blank rows to a
results area
 
A

abdulie

Thanks for your suggestions...much appreciated!

I ended up solving it by creating a command buttin with the following
code attached:

Private Sub CommandButton1_Click()


Dim cell As Range
Application.ScreenUpdating = False
Application.StatusBar = "Please be patient while we prepare your order
for printing...This should take approximately 1 minute"
With Sheets("ORDER FORM - MASTER")
For Each cell In .Range("A29:A1000")
If cell.Value = "" Then cell.EntireRow.Hidden = True
Next cell
..Cells.PageBreak = xlPageBreakNone

..PrintOut preview:=True
..Range("A29:1000").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub


Cheers
:)
 
K

Ken Johnson

Hi abdulie,
I can't fully understand why Autofilter won't do. However, the
following macro will ask you to select the heading of the column where
you are wanting rows with nonnumeric values to be hidden. It will then
do so for all the cells below the selected heading cells. If your
heading occupies more than one row make sure you select the whole
heading.

Public Sub HIDE_NONNUMERIC_QUANTITIES()
Dim C As Range
Dim stMessage As String
Dim rngHEADING As Range
Dim rngMODIFY As Range
Dim intColumn As Integer
Dim intBase As Long
stMessage = "Select the heading of the column "
stMessage = stMessage & "with nonnumeric values to be hidden."
Set rngHEADING = Application.InputBox _
(prompt:=stMessage, Type:=8, Default:=Selection.Address)
If rngHEADING.Columns.Count > 1 Then
MsgBox "This macro can only work on a single column."
Exit Sub
End If
intColumn = rngHEADING.Column
intBase = 65537 - ActiveSheet.Range(Cells(65536, intColumn), _
Cells(65536, intColumn).End(xlUp)).Rows.Count
Set rngMODIFY = ActiveSheet.Range(Cells(rngHEADING.Rows.Count + 1, _
intColumn), Cells(intBase, intColumn))
Application.ScreenUpdating = False
For Each C In rngMODIFY
If Not (Application.IsNumber(C.Value)) Then C.EntireRow.Hidden = True
Next C
End Sub

To undo the hiding just click on any column header to select a whole
column then go Format>Rows>Unhide.

Ken Johnson
 
Top