Row Hiding
Row Hiding
(OP)
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
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





RE: Row Hiding
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
RE: Row Hiding
Else Cells(i, 47).EntireRow.Hidden = False
RE: Row Hiding
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
RE: Row Hiding
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
RE: Row Hiding
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
RE: Row Hiding
RE: Row Hiding
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.