Macro to Hide lines without data in a certain range

Claudette

New Member
Joined
Mar 7, 2002
Messages
13
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
0zGrid.BusApp.170x60.gif

This message was edited by Dave Hawley on 2002-03-12 07:44
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top