[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Excel Macro Challenge Pt3

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
Kelvin Sparks
Asked:
Kelvin Sparks
  • 11
  • 8
1 Solution
 
StephenJRCommented:
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
 
Kelvin SparksAuthor Commented:
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
 
StephenJRCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kelvin SparksAuthor Commented:
Uploaded
Sample.xlsm
0
 
Kelvin SparksAuthor Commented:
Hi, did you get a chance to look at this?
0
 
Kelvin SparksAuthor Commented:
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
 
StephenJRCommented:
What about unhiding all rows before running the code? This could be done at the start of the code.
0
 
Kelvin SparksAuthor Commented:
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
 
Kelvin SparksAuthor Commented:
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
 
StephenJRCommented:
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
 
StephenJRCommented:
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
 
Kelvin SparksAuthor Commented:
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
 
Kelvin SparksAuthor Commented:
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
 
StephenJRCommented:
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
 
Kelvin SparksAuthor Commented:
As we then start to hide rows, that line must be executed - if the row is now hidden, the loop fails.
0
 
StephenJRCommented:
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
 
Kelvin SparksAuthor Commented:
Attached. If you choose the second item on the page - select no - the problem occurs
Sample.xlsm
0
 
StephenJRCommented:
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
 
Kelvin SparksAuthor Commented:
That's cracked it. I'll ytake it from here. Thanks vcery much for your patience and perserverance.
0

Featured Post

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.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now