?
Solved

Excel Macro Challenge Pt3

Posted on 2012-04-12
19
Medium Priority
?
268 Views
Last Modified: 2012-04-24
Hi

This code below is working well to a point. The problem is that once a row is hidden, then you can never find it again to unhide. This line also fail after hiding the row as rFind.Address immediately becomes nothing.

Loop While rFind.Address <> sAddr


The main code itself.

Sub x()
 
Dim rFind As Range, sAddr As String, ws As Worksheet, r As Range, r1 As Range
Dim rProd As Range, rSheet As Range

With Sheets("Select Product")
    Set rProd = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With
With Sheets("Admin")
    Set rSheet = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With

For Each r In rProd
    For Each r1 In rSheet
        With Sheets(r1.Text).Range(r1.Offset(, 2) & ":" & r1.Offset(, 2))
            Set rFind = .Find(What:=r, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    If r.Offset(, 2) = "Yes" Then
                        rFind.EntireRow.Hidden = False
                    ElseIf r.Offset(, 2) = "No" Then
                        rFind.EntireRow.Hidden = True
                    End If
                    Set rFind = .FindNext(rFind)
                 Loop While rFind.Address <> sAddr
            End If
        End With
        sAddr = ""
        Set rFind = Nothing
    Next r1
Next r

End Sub
0
Comment
Question by:Kelvin Sparks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
19 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 37837105
I did wonder about your first point. Find will still work on hidden rows. What should be happening but isn't? Would probably help if you could re-post your workbook.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37839457
Thanks, will be weekend before I can get back to it. Have found a number of URLs claiming Find cannot see hidden cells - seems strange to me.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37839706
Now you mention it, I think you have to specify the LookIn parameter:

Set rFind = .Find(What:=r, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37847095
Uploaded
Sample.xlsm
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37859948
Hi, did you get a chance to look at this?
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37865109
I've identifies the issue, but don't know the answer. The find is looking at cells that are formulas referencing cells on other sheets. To correctly find the row I have to use Lookin:=xlValues, but to find values incells that are in rows that are hidden I need Lookin:=xlFormulas - which will not resolve the value from the referenced cells and is always nothing. How can I use xlFormulas to see into a hidden row the use xlValues to perform the Find?

Kelvin
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37866035
What about unhiding all rows before running the code? This could be done at the start of the code.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37867871
Yes, we could do that. What is code to unhide rows for the selected workbooks only? Also, I note when running - and there and many rows being set there is a lok of screen activity as it loops through the worksheets - is there a way to do the updates silently, so the screens do not flicker and keep chnaging - it does not need to display the workboook the code is running against.

Thanks


Kelvin
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37867894
An alternative to the hiding of rows would be to set the row height to 0.  The intent is to remove the rows of products not being used from the users sight rather than to restrict any access or functionality. Whats the code to use that as an alternative - I note that replacing .Hidden = True with .Height = 0 doesn't work
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37869909
Kelvin - I think setting the RowHeight to zero is equivalent to hiding the row so better to do that. Will post some code shortly.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37869959
I think you just need to insert the middle line below:
For Each r1 In rSheet
    Sheets(r1.Text).Rows.Hidden = False
        With Sheets(r1.Text).Range(r1.Offset(, 2) & ":" & r1.Offset(, 2))

Open in new window

0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37874130
OK, thenks This will enable me to set all rows, but I still have the issue of this line

Loop While rFind.Address <> sAddr

If the row is hidden, the rFind.Address no longer has a value so t it falues .

Using the Lookin:=xlFormulas would solve it, but the cells are addresses and need Lookin:=xlValues to return the values.

Kelvin
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37885225
If we are able to resolve the line

Loop While rFind.Address <> sAddr

as above we probably don't need to unhide anything. Is it possible to find all rows - store in a recordset for each sheet, then hide from the recordset data? That is do the rFind.Address for all cells in a worksheet, storing them into a recordset, then loop the recordset and hide - a bit of an overhead, but may well work - thoughts?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37885675
You write "If the row is hidden, the rFind.Address no longer has a value so t it falues" but first we are unhiding all the rows so I don't understand how this can happen.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37885678
As we then start to hide rows, that line must be executed - if the row is now hidden, the loop fails.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37885703
Sorry, I may have confused myself. Please could you post a workbook with the latest code and then I will try to sort this out once and for all?
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37885718
Attached. If you choose the second item on the page - select no - the problem occurs
Sample.xlsm
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37885942
OK, there may well be a Find solution but in the interests of expediency I have gone for a less elegant approach which does appear to work. By the way, in the Admin sheet the column for both sheets should be B, not C as is shown for Wholesale (although it might well be possible to dispense with that completely):
Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, r1 As Range
Dim rProd As Range, rSheet As Range, rCell As Range, n1 As Long, n2 As Long

' this line means code will only be triggered when column D changed
If Target.Column <> 4 Then Exit Sub

With Sheets("Select Product")
    Set rProd = .Range("B5", "B7")
End With
With Sheets("Admin")
    Set rSheet = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With

For Each r In rProd
    For Each r1 In rSheet
        'Sheets(r1.Text).Unprotect ("freshaz")
        With Sheets(r1.Text)
            n1 = .Range(r1.Offset(, 2) & Rows.Count).End(xlUp).Row
            For Each rCell In .Range(r1.Offset(, 2) & "1:" & r1.Offset(, 2) & n1)
                If rCell = r Then
                    If r.Offset(, 2) = "Yes" Then
                        rCell.EntireRow.Hidden = False
                    ElseIf r.Offset(, 2) = "No" Then
                        rCell.EntireRow.Hidden = True
                    End If
                End If
            Next rCell
        End With
        'Sheets(r1.Text).Protect ("freshaz")
    Next r1
Next r

End Sub

Open in new window

0
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 37889461
That's cracked it. I'll ytake it from here. Thanks vcery much for your patience and perserverance.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question