Solved

Excel Macro Challenge Pt3

Posted on 2012-04-12
19
254 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
  • 11
  • 8
19 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 22

Author Comment

by:Kelvin Sparks
Comment Utility
Uploaded
Sample.xlsm
0
 
LVL 22

Author Comment

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

Author Comment

by:Kelvin Sparks
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
That's cracked it. I'll ytake it from here. Thanks vcery much for your patience and perserverance.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now