Hi everyone

It is my first post here. Ive been consulting this site and it is very
helpful and Ive learned numerous relevant tips.

I want to set up an Order Form (with all items, price etc) but this
document is HUGO (more than 1000 items) and it's a pain in the back to
print.

I need to build a macro that will check every line (to see if there's
anything ordered) and where ever there is units (to keep it) and where
there's no unit, simply hide or delete the row..

let me know if this make any sens
any help would be greatly appreciated

thank you very much in advance..
jim barber


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

try this for whichever row has the empty cells in it:

Range("A1").select
1 Do until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

Selection.EntireRow.Delete
ending = ending + 1
If ending 1000 Then
GoTo 2
End If
goto 1

2 end sub

this should go through the do loop and when it hits an
empty cell it will break the loop and delete the row,
then repeat the loop. the ending clause is how many rows
it will delete.

hope it helps

Jim,

Another way (with the units in column H),

'-----

Option Explicit
Sub hideRows()
Range("H:H").SpecialCells(xlCellTypeBlanks).Entire Row.Hidden = True
End Sub

Sub showRows()
Rows.Hidden = False
End Sub

'-----

HTH
Anders Silvén

"jbarber" skrev i meddelandet ...

Hi everyone

It is my first post here. Ive been consulting this site and it is very
helpful and Ive learned numerous relevant tips.

I want to set up an Order Form (with all items, price etc) but this
document is HUGO (more than 1000 items) and it's a pain in the back to
print.

I need to build a macro that will check every line (to see if there's
anything ordered) and where ever there is units (to keep it) and where
there's no unit, simply hide or delete the row..

let me know if this make any sens
any help would be greatly appreciated

thank you very much in advance..
jim barber


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

This didn't work for me. When I had non-blank values in H:H it hid the
blank rows up to the last non-blank cell and none beyond. When I had
all blank values in H:H I got a run-time error.

"Anders S" <anders wrote in message ...
Jim,

Another way (with the units in column H),

'-----

Option Explicit
Sub hideRows()
Range("H:H").SpecialCells(xlCellTypeBlanks).Entire Row.Hidden =
True
End Sub

Sub showRows()
Rows.Hidden = False
End Sub

'-----

HTH
Anders Silv n

"jbarber" skrev i meddelandet
...

Hi everyone

It is my first post here. Ive been consulting this site and it is very
helpful and Ive learned numerous relevant tips.

I want to set up an Order Form (with all items, price etc) but this
document is HUGO (more than 1000 items) and it's a pain in the back to
print.

I need to build a macro that will check every line (to see if there's
anything ordered) and where ever there is units (to keep it) and where
there's no unit, simply hide or delete the row..

let me know if this make any sens
any help would be greatly appreciated

thank you very much in advance..
jim barber


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

I would approach this very differently.

1) Create your list as you seem to have done.

2) Create an order form on another sheet and use VLookup (possibl
incorporating DataValidation to "pick from list". So in a cell of you
choice you would type/select the product and other details would sel
populate.

3) More advanced using VBA would be a userform with a listbox showin
products to select from

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Jim,

The original macro just hides rows in the used range, the unused 60,000+ rows will not print anyway, so I didn't bother about them.

If you must hide *all* empty rows, you can try the addition below.

'-----
Sub hideRows()
On Error GoTo hExit
Range("H:H").SpecialCells(xlCellTypeBlanks).Entire Row.Hidden = True
Range(Rows(Cells(Rows.Count, "H").End(xlUp).Row + 1), _
Rows(Rows.Count)).EntireRow.Hidden = True
hExit:
End Sub
'-----

Regards,
Anders Silvén

"onedaywhen" skrev i meddelandet om...
This didn't work for me. When I had non-blank values in H:H it hid the
blank rows up to the last non-blank cell and none beyond. When I had
all blank values in H:H I got a run-time error.

"Anders S" <anders wrote in message ...
Jim,

Another way (with the units in column H),

'-----

Option Explicit
Sub hideRows()
Range("H:H").SpecialCells(xlCellTypeBlanks).Entire Row.Hidden =
True
End Sub

Sub showRows()
Rows.Hidden = False
End Sub

'-----

HTH
Anders Silv n

"jbarber" skrev i meddelandet
...

Hi everyone

It is my first post here. Ive been consulting this site and it is very
helpful and Ive learned numerous relevant tips.

I want to set up an Order Form (with all items, price etc) but this
document is HUGO (more than 1000 items) and it's a pain in the back to
print.

I need to build a macro that will check every line (to see if there's
anything ordered) and where ever there is units (to keep it) and where
there's no unit, simply hide or delete the row..

let me know if this make any sens
any help would be greatly appreciated

thank you very much in advance..
jim barber


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

thanks for all replies.. but maybe I adventured myself in a 'far beyond
my knowledge' situation...

here attached excel document is exactly what I use as an order form.

please see the attached worksheet.
You see there is items ordered in row 5 to 9 and 19 to 23.

in the TOTAL (column L) I want all the rows with a ZERO to be hided! So
I could ONLY print the row with items ordered ...

let me know what you think about it...

thank you very much, I feel like I still can pull this one with your
help...

jim b.
:)


+----------------------------------------------------------------+
| Attachment filename: jbarber_example_excel.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=361879|
+----------------------------------------------------------------+

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/