Hide blank rows on each page excel

M

magpie2000k

Board Regular
Joined
Sep 13, 2013
Messages
196
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..
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Re: Help to hide black rows on each page excel

How about
Code: 
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
Click to expand...
It is indeed
Bearing that in try this instead
Code: 
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
hide xrange and protect cells
Replies
2
Views
172
Peter_SSs
Peter_SSs
V
  • Question
VBA Command to create new worksheets with dates as worksheets name based on certain conditions
Replies
1
Views
140
Volrin
V
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
195
mumps
mumps
R
  • Question
VBA Progress bar
Replies
4
Views
746
Rhemo
R
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
226
birdman15
B

Forum statistics

Threads
1,214,630
Messages
6,120,634
Members
448,973
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top