×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Row Hiding

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

RE: Row Hiding

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

RE: Row Hiding

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

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

RE: Row Hiding

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

RE: Row Hiding

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

RE: Row Hiding

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

RE: Row Hiding

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

RE: Row Hiding

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources