hiding rows

[cufc1210]

I have data in cells A7K394 which all have 0 in them until totals are added
in other worksheets which then pullthrough.

At the end of the week I have to Hide all rows from A7 to A394 that still
have 0 in them as no totals have been added in other sheets so the customer
has not posted with us.

is there any way excell can recognise the cells that have no data against
them and hide them automatically by pressing a button. or if they all start
off hidden unhide as data is entered.

Hopefully this makes sense

many thanks for any help with this as again its to save me lots of manual work

 

[Gord Dibben]

Sub HideBlank_Zeros_Rows()
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("A1", Range("A" & Rows.Count). _
End(xlUp).Address)
For Each i In RngCol
If i.Value = "" Or i.Value = "0" Then _
i.entirerow.Hidden = True
Next i
End Sub


Gord Dibben MS Excel MVP

 

[cufc1210]

Hi Gordon thanks for the reply but im a bit thick at this

i have i think made it easier the data now totals into rows DL7 down to DL
400 so any row with a 0 in this range i want hidden.

how to i put this into the formula you posted, and do I just copy the
formula into the VBA and it works straight away.

Sorry if this looks a bit dumb

 

[Gord Dibben]

As another poster suggested, autofilter will do the trick but if you want a
macro..................

Sub Hide_Zeros_Rows()
Dim Rng As Range
Dim i As Range
Set Rng = Range("DL7L400")
For Each i In Rng
If i.Value = 0 Then _
i.EntireRow.Hidden = True
Next i
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.



Gord

 

[cufc1210]

Thanks Gordon that worked a treat and will save me lots of work

cufc1210

 

[Gord Dibben]

Good to hear

Thanks for the feedback

Thanks Gordon that worked a treat and will save me lots of work

cufc1210