hiding rows

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

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

On Sun, 25 Apr 2010 10:05:01 -0700, cufc1210
wrote:

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

Filter column a where your zeroes appear. In 2007 this allows you to select
all(by default) then deselect zeroes in this case which should give you the
desired result. Can't remember whether 2003 provides this.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"cufc1210" wrote:

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

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" wrote:

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

On Sun, 25 Apr 2010 10:05:01 -0700, cufc1210
wrote:

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

.

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 InsertModule. 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 ToolMacroMacros.

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



Gord



On Sun, 25 Apr 2010 14:34:01 -0700, cufc1210
wrote:

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" wrote:

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

On Sun, 25 Apr 2010 10:05:01 -0700, cufc1210
wrote:

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

.

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

cufc1210

"Gord Dibben" wrote:

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 InsertModule. 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 ToolMacroMacros.

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



Gord



On Sun, 25 Apr 2010 14:34:01 -0700, cufc1210
wrote:

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" wrote:

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

On Sun, 25 Apr 2010 10:05:01 -0700, cufc1210
wrote:

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

.


.

Good to hear

Thanks for the feedback

On Wed, 28 Apr 2010 08:03:02 -0700, cufc1210
wrote:

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

cufc1210

"Gord Dibben" wrote:

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 InsertModule. 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 ToolMacroMacros.

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



Gord



On Sun, 25 Apr 2010 14:34:01 -0700, cufc1210
wrote:

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" wrote:

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

On Sun, 25 Apr 2010 10:05:01 -0700, cufc1210
wrote:

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

.


.