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.O
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
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.O
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
StephenJR
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.
ASKER
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.
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)
Set rFind = .Find(What:=r, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
ASKER
Uploaded
Sample.xlsm
Sample.xlsm
ASKER
Hi, did you get a chance to look at this?
ASKER
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
Kelvin
What about unhiding all rows before running the code? This could be done at the start of the code.
ASKER
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
Thanks
Kelvin
ASKER
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
Kelvin - I think setting the RowHeight to zero is equivalent to hiding the row so better to do that. Will post some code shortly.
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))ASKER
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
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
ASKER
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?
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?
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.
ASKER
As we then start to hide rows, that line must be executed - if the row is now hidden, the loop fails.
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?
ASKER
Attached. If you choose the second item on the page - select no - the problem occurs
Sample.xlsm
Sample.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's cracked it. I'll ytake it from here. Thanks vcery much for your patience and perserverance.