If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
hiding rows
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 |
#3
|
|||
|
|||
hiding rows
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 |
#4
|
|||
|
|||
hiding rows
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 . |
#5
|
|||
|
|||
hiding rows
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 . |
#6
|
|||
|
|||
hiding rows
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 . . |
#7
|
|||
|
|||
hiding rows
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 . . |
Thread Tools | |
Display Modes | |
|
|