Hide Rows.

[Houstons_Time]

Can some one help me with a macro - Where I hide the entire row if that PARTICULAR ROW contains NO DATA or NUMBER.

 

[HalfAce]

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]

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!

 

[brian.wethington]

What kind of range are we talking about working with here?

 

[Houstons_Time]

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

 

[VoG]

Try this - assumes all columns have the same number of populated rows

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]

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

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]

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]

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]

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