Hide blank rows on each page excel

[magpie2000k]

Ye I mean Blank not Black

Hi I have used the below VB code and i can get it to work to hide blank rows on sheet 1 where the contents in A is blank

However I need to get it to run for all pages in the workbook. of which there are 4

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160913
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("A1:A70")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True

Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
End Sub

Any help would be great..

 

[magpie2000k]

Re: Help to hide black rows on each page excel

HA HA im off to twickers Sat!!!!

https://www.dropbox.com/s/mu9j0r0ioyrld69/existing pipeline.xlsm?dl=0

 

[Fluff]

Re: Help to hide black rows on each page excel

I trust you'll be wearing white, rather than red!

 

[magpie2000k]

Re: Help to hide black rows on each page excel

Indeed however I have a red going with me... grrrr never mind will be good to see his face after the match..

 

[Fluff]

Re: Help to hide black rows on each page excel

In that case I'm happy to continue helping you

Try this (I'd made a basic mistake in the original). Because of all the formulae, I've turned of calculation temporarily, whilst the macro runs

Sub HideRows()

   Dim Ws As Worksheet
   Dim Cl As Range
Application.Calculation = xlCalculationManual
   
   For Each Ws In Worksheets
      Ws.Rows("1:120").Hidden = False
      For Each Cl In Ws.Range("A1:A120")
         If Cl.Value = "" Then Cl.EntireRow.Hidden = True
      Next Cl
   Next Ws
Application.Calculation = xlCalculationAutomatic
End Sub

 

[magpie2000k]

Re: Help to hide black rows on each page excel

Boom.......
Absolutely wonderful....
You not going Sat?????
I will have a guinness for you before and after if not..

 

[Fluff]

Re: Help to hide black rows on each page excel

Glad to help & thanks for the feedback.

I'll be down my local to watch it.

 

[magpie2000k]

Re: Help to hide black rows on each page excel

I want to add two more columns to my report so that it pulls the product type from Column U in the database export and the number of items from column V. It is for another of our brands hence teh name change of database

The two possible options are Subscriptions or licences in column U and in column V it is just a number

I have in C at present
=IFERROR(INDEX('LCEL Database.csv'!$B$2:$B$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$8:C8))),"")

In D at present
=IFERROR(INDEX('LCEL Database.csv'!$H$2:$H$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$9:C9))),"")

In E at present
IFERROR(TODAY()-INDEX('LCEL Database.csv'!$G$2:$G$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$9:C9)))&" days",""

In F at present
=IFERROR(TODAY()-INDEX('LCEL Database.csv'!$G$2:$G$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$9:C9)))&" days","")

I would like Subscriptions and Licences shown in colum G and Number of items in Column H

Lastly if it is at all possible I would like the items grouped so that the resulting list put all Subscriptions at the top and Licences below

Not sure if that is possible.

Thanks again

 

[Fluff]

Re: Help to hide black rows on each page excel

You'd best start a thread for this, as formulae are not my strong point

 

[ArtySin]

Re: Help to hide black rows on each page excel

Hi,
I've got just one sheet and need to hide rows from row 4 down where there are no values less than 1. I tried using the vba below and changing a few things but no luck unfortunately. Here a Dropbox link to the w/book, hope you Guys can help.
Many thanks
Summary of Results.xlsm

Sub HideRows()

Dim Ws As Worksheet
Dim Cl As Range
Application.Calculation = xlCalculationManual

For Each Ws In Worksheets
Ws.Rows("4:37").Hidden = True
For Each Cl In Ws.Range("A4:A37")
If Cl.Value = >0 Then Cl.EntireRow.Hidden = False
Next Cl
Next Ws
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Fluff]

Re: Help to hide black rows on each page excel

@ArtySin
A few things
1) As this thread is still live, please start a thread of your own.
2) When you do, please do not forget to mention that you have crossposted this question & provide the relevant links. (you have already been warned about crossposting)
3) The file you have posted needs to be marked for sharing. At the moment it's asking me to log-in.