Row Hiding

[Harley78]

I have created a command button to hide rows.... depending on the cell. However, I am trying to unhide the rows but stuck as to how to achieve this. I also want the button to change names each time you select it. eg. All Data / Main Data. The macro I have thus far is which works great. My apologies if this is repetative...

Private Sub CommandButton1_Click()

Dim LastRow As Long, i As Long

LastRow = Cells(5000, 47).End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
If Cells(i, 47).Value = "" Then Cells(i, 47).EntireRow.Hidden = True
Next i
Application.ScreenUpdating = True

End Sub

 

[yogia]

Hi Harley78:

If I understand you correctly ...

Cells(i, 47).EntireRow.Hidden = True will hide row number i
and
Cells(i, 47).EntireRow.Hidden = False will unhide row number i

If I misunderstood your question, my apologies.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[IFRs]

Does adding this to the end of the if statement help?

Else Cells(i, 47).EntireRow.Hidden = False

 

[Harley78]

Hey Yogi

When I click on the button it hides all rows when Cell 47 (column AU)is blank (""). I labeled it Main Data, But now I am trying to add is when you click on "Main Data" Button, it condences and the button now says "All Data" and it opens up every row. The button then will switch to Main Data.

Hope that helps

 

[Harley78]

Hey IFR,

I placed it like it is now, at the end and after Next i and it doesn't work!

Private Sub CommandButton1_Click()

Dim LastRow As Long, i As Long

LastRow = Cells(5000, 47).End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
If Cells(i, 47).Value = "" Then Cells(i, 47).EntireRow.Hidden = True

Else if Cells(i, 47).EntireRow.Hidden = False

Next i
Application.ScreenUpdating = True

End Sub

 

[Harley78]

OK,,, I GOT THE BUTTON TO HIDE ROWS AND SWITCH NAMES, BUT STILL DON'T KNOW WHAT I AM DOING WRONG IN REGARDS TO OPENING UP ALL ROWS.... Can you let this dummy know what it is that I am missing?

Private Sub CommandButton1_Click()

Dim LastRow As Long, i As Long

LastRow = Cells(5000, 47).End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
If CommandButton1.Caption = "ALL DATA" Then HideTheRows = False
If Cells(i, 47).Value = "" Then Cells(i, 47).EntireRow.Hidden = True

Next i

If CommandButton1.Caption = "MAIN DATA" Then HideTheRows = True
If Cells(i, 47).Value = "" Then Cells(i, 47).EntireRow.Hidden = False

If HideTheRows Then
CommandButton1.Caption = "ALL DATA"
Else
CommandButton1.Caption = "MAIN DATA"
End If
End Sub

 

[melone]

Instead of using Cells, can you use Rows? I haven't tried it, but thought I would throw it out there.

 

[Harley78]

Melone

Here is a brief summary of what this spreadsheet does.... it takes eg. row 3 and all data is entered (main data)Cell A:CF[currently]. Depending on the breakdown information which is underneath, A:AQ Now how many rows...some tests could be more than others.. Then after all data is entered on the first test,,, the second test is done....and all data entered like above.. So now you can see that if I want to see just the main data just click a button,,, if I want to see all data, I just click again... So it is unknown how many rows we will need between each main test data. Hope this helps enlighten as to what I am trying to achieve.