Macro to Hide lines without data in a certain range

**Claudette** · Mar 12th, 2002, 08:38 AM

I have a spreadsheet with a looong list of part numbers in column A. At the end of the day, I need to print only the lines that have data in the area of the spreadsheet where I enter quantities, because the entire parts list is much too long to print. Usually there are only ten rows with data in them to print, so I have to go through a lot of work to manually hide them. Anyone know of a way that I can write a macro to go through a range and hide all the rows that have no data in them? I could name the range with the quantities in it "quantities" and have the macro run row by row. The other good thing is that to the right of the quantities range, I have a column with a count going for each row. I could also have my macro go through that column, and hide any rows where the cell value is 0. Does anyone know how I can program this? I know programming, but for me the syntax is always alien...I know I can run a macro recorder to get the syntax for hiding a row, but how do I write the if statement and how do I tell the macro to go to the last row in teh spreadsheet and then stop??

Thanks!

Claudette

Mar 12th, 2002, 08:42 AM

Example :-

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

**Dave Hawley** · Mar 12th, 2002, 08:43 AM

Hi Claudette

I hate loops, but this should do if you want a quick answer. If not try here:
http://www.ozgrid.com/VBA/VBACode.htm

Sub HideIfEmpty()
Dim rRows As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each rRows In Range(Selection.Cells(1, 1) _
, Selection.Cells(65536, 1).End(xlUp))
rRows.EntireRow.Hidden = (rRows = 0)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-12 07:44 ]

**Claudette** · Mar 12th, 2002, 08:49 AM

On 2002-03-12 07:42, Anonymous wrote:
Example :-

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

I don't understand what this does, or where I should put it?

**Dave Hawley** · Mar 12th, 2002, 08:52 AM

Claudette,

To use any or all of the above code:

Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.

Mar 12th, 2002, 08:54 AM

What object in the photograph are you referring to?

**Claudette** · Mar 12th, 2002, 10:32 AM

When I ran the macro, the program gave me this error:

Runtime error 1004:
Application-defined or object-defined error

And when I clicked on "Debug" it highlighted the part of hte text that says"

For Each rRows In Range(Selection.Cells(1, 1) _
, Selection.Cells(65536, 1).End(xlUp))

Help!!