Hide Rows.

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
Can some one help me with a macro - Where I hide the entire row if that PARTICULAR ROW contains NO DATA or NUMBER.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Houstons_Time, welcome to the board.
Are you looking to hide the row(s) that are completely blank, or just the one(s) that
are blank in one or more specified column(s)?
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
Yes, You are correct - I am looking for some thing completly BLANK and automatically unhides the cell as soon as information is added from the other linked sheets.

For instance - Master sheet and Slave Sheet.

- Rows from Slave are linked to Master.
- On Master Empty Rows should not be visible until DATA is added on a Slave Sheet (Same Linked Cells).

I hope you understad, What I am trying to explain. Thanks for your help!
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13

ADVERTISEMENT

We could say all the rows on a sheet with NO DATA/NUMBERS present in them.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this - assumes all columns have the same number of populated rows

Code:
Sub HideBlankRows()
Dim LastRow As Long, I As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For I = 1 To LastRow
    If WorksheetFunction.CountA(Rows(I)) = 0 Then Rows(I).EntireRow.Hidden = True
Next I
Application.ScreenUpdating = True
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

I think I understand. The rows on Master should be hidden until an entry is made in that
same row on the Slave sheet. (yes?)
Here's something that will unhide the Master row when an entry is made in Slave, and
re-hide the row in Master if the data in that row on Slave is deleted, resulting in a
completely blank row again.
To install this, right click the sheet tab for the Slave sheet and choose View code.
Copy the code from here & paste it into the white area that is the sheet module.
(Press Alt+Q to get back to your sheet after pasting.)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim ThisRow As Long
ThisRow = Target.Row
Sheets("Master").Rows(ThisRow).Hidden = _
  Application.WorksheetFunction.CountA(Rows(ThisRow).EntireRow) = 0
End Sub

Note, this is designed to only work on single cell changes in your Slave sheet.

Hope it helps.
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
Thanks a million folks.... This has really helped me... One last question, How can I protect my formula's in an edittable cell.

The problem which I face is, if i delete or erase any thing in the cell, this deletes the formula as well...

Any help with this!
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
What about the cell do you want to be editable. There are some tricks out there but for the most part an editable cell is completely editable. You can force passwords, you can protect the sheet and allow certain things though.
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
This is a little tricky here.

for instance Cell A1 (Slave Sheet) is linked to cell A1 (Master Sheet). Now if I try to edit any thing in cell A1 (Master Sheet), this takes away the link..

What I am trying to do is - The link should not be erased/deleted in any circumstances, even if Cell A1(Master Sheet) data is erased or changed.

Is there any macro or trick for this...

Please help...

Thanksss
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,567
Members
414,389
Latest member
MarkElla

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
Top