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

 

[Fluff]

Re: Help to hide black rows on each page excel

How about

Sub HideRows()

   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Ws.Rows("1:70").Hidden = False
      Ws.Range("A1:A70").SpecialCells(xlBlanks).EntireRow.Hidden = True
   Next Ws
End Sub

This needs to be run manually, rather than being triggered by an event.

 

[magpie2000k]

Re: Help to hide black rows on each page excel

So I paste this at workbook level and then go onto each page and double click a cell and hit enter. for each worksheet?

is that correct.. I have steered clear of VB as im a newbie in the VB world

 

[Fluff]

Re: Help to hide black rows on each page excel

No, the macro needs to go in a standard module & when you run the macro it will work on all sheets in the workbook.
Have a look at this article which shows how to install & run a macro
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

 

[magpie2000k]

Re: Help to hide black rows on each page excel

want to say followed steps exactly but somethign tells me i messed up.

However I have follwoed the guide three times.

When I press ALT F8 I get the Macro box pop up and I run it..

Nothing gets hidden

Where am I going wrong

Is it because indeed the row is not empty as the cells contain a formula even one that returns no result.

 

[Fluff]

Re: Help to hide black rows on each page excel

Is it because indeed the row is not empty as the cells contain a formula even one that returns no result.

It is indeed
Bearing that in try this instead

Sub HideRows()

   Dim Ws As Worksheet
   Dim Cl As Range
   
   For Each Ws In Worksheets
      Ws.Rows("1:70").Hidden = False
      For Each Cl In Range("A1:A70")
         If Cl.Value = "" Then Cl.EntireRow.Hidden = True
      Next Cl
   Next Ws
End Sub

 

[magpie2000k]

Re: Help to hide black rows on each page excel

That works but I need to run it on each page in the work book by selecting it and alt f8 and doing so undoes the filter on the previous page

argggghh im losing my marbles.


Thanks

Is it because the worksheets are not numbered they have names?

 

[Fluff]

Re: Help to hide black rows on each page excel

By page you mean worksheet?
Also in you OP you made no mention of either formulae or filters. Is there anything else that you haven't mentioned? like sheet protection, or merged cells.
Also if you have autofilters, what row are they in?

 

[magpie2000k]

Re: Help to hide black rows on each page excel

Hi Sorry.

Yes worksheet the spreadsheet has 4 tabs(worksheets named)
There are some merged cells but I have unmerged them and same result it works fuilly on each worksheet but doesnt do all worksheets
There are no filters

The data starts on each sheet in B2 and each sheet is identical in layout it has 5 sales persons names with a bunch of rows (25 to be exact under each persons name
all sheets have the same formulas

No sheet protection

 

[Fluff]

Re: Help to hide black rows on each page excel

The macro should hide the relevant rows on each sheet(tab) whenever you run it.
If it is not doing that, do you get any error messages?