Updating Visible Cells

[reberryjr]

I have some code that filters column H for a series of values. Then, I need to update the filtered results to "Denied". For some reason, the code I'm using is updating everything, including the header row to "Denied". It's odd, b/c I use this snippet elsewhere, and there isn't an issue. When I look up how to perform the update I'm trying to, everything I've found points to using the .SpecialCells(xlCellTypeVisible).

If mDLR > 1 Then mD.Range("H2:H" & mDLR).SpecialCells(xlCellTypeVisible).Value = "Denied"

• The filter is working correctly.
• The filter returns 1 value, making the Last Row (mDLR) = 2.

I can't imagine why, but the only thing I can think of, is the last row being row 2 is causing the issue. It's the only difference between the successful iterations and this one that's failing.

 

[rlv01]

I ran your code on some cobbled up filtered data and I'm not seeing that behavior. All is as expected, even with the last row at 2. Suggest you add debug code to let you see if the range addresses are as you expect.

    Dim rng As Range
    Dim Msg As String
    If mDLR > 1 Then
        Set rng = mD.Range("H2", mD.Range("H" & mD.Rows.Count).End(xlUp))
        Msg = "Range: " & rng.Address & vbCr
        Msg = Msg & "Visible Range: " & rng.SpecialCells(xlCellTypeVisible).Address & vbCr
        Msg = Msg & "mDLR: " & mDLR
        MsgBox Msg, , "Debug"
        
        mD.Range("H2:H" & mDLR).SpecialCells(xlCellTypeVisible).Value = "Denied"
    End If

 

[reberryjr]

I ran your code on some cobbled up filtered data and I'm not seeing that behavior. All is as expected, even with the last row at 2. Suggest you add debug code to let you see if the range addresses are as you expect.

    Dim rng As Range
    Dim Msg As String
    If mDLR > 1 Then
        Set rng = mD.Range("H2", mD.Range("H" & mD.Rows.Count).End(xlUp))
        Msg = "Range: " & rng.Address & vbCr
        Msg = Msg & "Visible Range: " & rng.SpecialCells(xlCellTypeVisible).Address & vbCr
        Msg = Msg & "mDLR: " & mDLR
        MsgBox Msg, , "Debug"
       
        mD.Range("H2:H" & mDLR).SpecialCells(xlCellTypeVisible).Value = "Denied"
    End If

This is the result of the snippet you provided.

 

[reberryjr]

This is the result of the snippet you provided.
View attachment 107015

If I sort the data so that the cells that will be updated are not in row 2, the code works fine. It seems to only fail if the cell being updated is in row 2. Obviously, I don't want to always have to sort data just to filter it, so is there some sort of solution involving row2?

 

[rlv01]

You could modify your code so it tests for a row 2 scenario.

    Dim rng As Range
    Dim rngVisible As Range
    
    If mDLR > 1 Then
        Set rng = mD.Range("H2", mD.Range("H" & mD.Rows.Count).End(xlUp))
        Set rngVisible = rng.SpecialCells(xlCellTypeVisible)
        
        'Special case test
        If rng.Address(0, 0) <> "H1:H2" Then
            If rng.Rows.Count = 1 Then
                Set rngVisible = rng 'Special case
            End If
            rngVisible.Value = "Denied"
        Else
            'null filter result, do nothing
        End If
    End If

 

[reberryjr]

You could modify your code so it tests for a row 2 scenario.

    Dim rng As Range
    Dim rngVisible As Range
   
    If mDLR > 1 Then
        Set rng = mD.Range("H2", mD.Range("H" & mD.Rows.Count).End(xlUp))
        Set rngVisible = rng.SpecialCells(xlCellTypeVisible)
       
        'Special case test
        If rng.Address(0, 0) <> "H1:H2" Then
            If rng.Rows.Count = 1 Then
                Set rngVisible = rng 'Special case
            End If
            rngVisible.Value = "Denied"
        Else
            'null filter result, do nothing
        End If
    End If

I do a lot of filtering. I'm not keen on having to add this much code every time I filter. I appreciate the help!!!

 

[rlv01]

Not sure how you made the jump from what I posted to "every time I filter". This is to solve the particular problem you posted.