VBA - Unhide most recently hidden row?

[dropkickweasel]

I have a workbook that lists products.
One of the columns has a drop-down box to determine if each product is 'fresh' or 'destroyed'.
If a product is marked as 'destroyed', then the row of that product is hidden on the sheet.
This is the macro that automatically hides the row when the product selection is set to 'destroyed'.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit code if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   See if column H was updated to "Destroyed"
    If Target.Column = 8 And Target = "Destroyed" Then
        Rows(Target.Row).Hidden = True
    End If

End Sub

In the event that I accidentally set a product to 'destroyed', I would like a button on the worksheet that I can click to unhide the most recently hidden row.
This won't necessarily be the final row in the worksheet.
If I catch it immediately, it will be the row that was most recently modified by changing 'fresh' to 'destroyed'.

Here's the code I'm using at the moment:

Sub UnhideLastHiddenRow()
Dim lRow As Long
 With Range("H:H").SpecialCells(xlCellTypeVisible)
 lRow = .Areas.Count
 .Areas(lRow).Cells(1, 1).Offset(-1, 0).EntireRow.Hidden = False
 End With
End Sub

Column H is the column with 'destroyed' in it.

It's not working as I want it to.
I believe it is unhiding the last hidden row (as in, the row that appears last on the worksheet), not the last hidden row (as in the row that was hidden most recently, regardless of where it appears on the worksheet).

I am not a confident VBA user and have frankensteined the code together from research and kind strangers.
Does anyone have a way to adapt the code to unhide the most recently hidden row, not the lowest hidden row on the worksheet?

Many thanks.

 

[igold]

If you wanted to use a helper cell you could do something like this. I used cell AZ1. It will always capture the last time the code was run.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit code if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   See if column H was updated to "Destroyed"
    If Target.Column = 8 And Target = "Destroyed" Then
        Rows(Target.Row).Hidden = True
        Range("AZ1") = Target.Row
    End If

End Sub

Sub UnhideLastHiddenRow()
    Rows(Range("AZ1").Value).Hidden = False
End Sub

 

[dropkickweasel]

That's fantastic - it seems so simple and elegant to me with very limited VBA experience or understanding. Thank you!

This will work just fine if I am able to spot my mistake after making just one, otherwise cell AZ1 will be overwritten with only the most recent mistake and I will have to find another way to unhide previous rows hidden by mistake.

Follow up question:

Thinking of trying to 'future proof' this a little for other end users, is there any way to modify the first part of the code to write into the next empty cell in column AZ when hiding a row, then for the second part to look at column AZ, take the row reference from the lowest written cell as the row to unhide, then delete that row reference from column AZ so that the next time the macro is run it takes the next lowest row reference instead?

I'm guessing that the "Range" value would need to be changed from "AZ1" to whatever code is needed to say "the next empty cell in column AZ" for the first part.
And for the second the reverse - "the last non-empty cell in column AZ", then add an extra bit to delete whichever cell it finds in that range.
Sadly, I don't know how to do that, so if you can make any suggestions that would be grand. Otherwise, thank you so much for your help so far :]

For example:
If I hide row 17, 17 is written to AZ1.
If I then hide row 23, 23 is written to AZ2 rather than overwriting AZ1.

If I then realise I've made a mistake, the second macro checks all of column AZ, recognises AZ2 as the lowest cell with a value in it, unhides row 23, then deletes 23 from AZ2.
I can then run the macro again, now the second macro recognises AZ1 as the lowest cell with a value in it, unhides row 17, then deletes 17 from AZ1?

Does that sound like something that's possible to achieve?

 

[dropkickweasel]

Update:

I've tried to modify the code myself with some internet research. Here's what I've managed to produce so far:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit code if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   See if column H was updated to "Destroyed"
    If Target.Column = 8 And Target = "Destroyed" Then
        Rows(Target.Row).Hidden = True
        Range("AZ" & Rows.Count).End(xlUp).Offset(1) = Target.Row
    End If

End Sub

This seems to work fine initially, but it is not without its issues. It seems that if the target value is written onto a hidden row, that the End(xlUp) command doesn't read that value and moves on to the next one.

Is there any way to make sure this code always reads the bottom value of column AZ, even if the bottom value is written to a hidden row?

If not, I guess I can make row 1 the target range, as that row will never be hidden.

By changing the Range in the same way on the second macro, I can unhide the most recently hidden row still, and am now looking to work out how to delete the value in that cell so that it is not found on the next run through.

 

[dropkickweasel]

Solved (I think).

I wasn't sure if it was possible to not ignore hidden rows when looking for the most recent value, but I was able to change the target range to a sheet that will never have hidden rows and it now seems to be working as it should.

I was also able to find the little bit of syntax needed to delete each entry as a row is hidden.

Thank you so much to igold for pointing me in the right direction :]

 

[igold]

You are welcome, I was happy to help, thanks for the feedback.

You could try writing across row 1 with subsequent values each time time the code is run and they using a Rank function to find a particular "Destroyed".

If you need more help come on back...