none
last item in column missed if using filter(s) RRS feed

  • Question

  • I've written the code below (thanks again for all the great help from this group) to update cells dependent on a validation list. My test example is a list of pets (animal type) and the validation list is the available types of animals. This works perfectly unless I select the filter option on the column of pets and uncheck (filter out) an item that happens to be in the last cell of the list. Below is the validation table and two instances of the pets list. The first is unfiltered, and the second filters out "dog" by unchecking from the filter for that column. If I change "dog" to "K9" in the validation list, every instance of "dog" will be successfully replaced with "K9" in the pets list. If the last cell in the pets list is "dog" and "dog" is unchecked (filtered) from the list, and I again change "dog" to "K9" in the validation list, every instance of "dog" is replaced with "K9" except for the instance in the last cell. It remains "dog." I considered disabling all filters in the code before the replace happens, but it would helpful to allow filters to remain active, and the replacement is successful on even the instances not shown (filtered out) except the last one. I think it has something to do with the way the Range is selected--it's missing the last cell in the column list. Is there a way to extend the Range selection to ensure it includes all the cells containing data? Here are the lists and current code. 

    Thanks!

    Validation list:

    Animals
    bird
    cat
    dog
    fish

    Pet list below contains 4 instances of "dog." Note one of them is the last cell. 

    Pet
    cat
    dog
    bird
    dog
    fish
    cat
    dog
    cat
    dog

    Changed "dog" to "K9" in validation list

    Animals
    bird
    cat
    K9
    fish

    "dog" successfully replaced with "K9"

    Pet
    cat
    K9
    bird
    K9
    fish
    cat
    K9
    cat
    K9

    Unchecked "dog" from filter on pets list

    Pet
    cat
    bird
    fish
    cat
    cat

    Changed "dog" to "K9" again, turned off filter to show all cells, and all instances of "dog" were replaced except the last one. 

    Pet
    cat
    k9
    bird
    k9
    fish
    cat
    k9
    cat
    dog

    Here's the code

    Option Explicit

    Dim OldValue As String
    Dim NewValue As String
    Dim AnimalListCells As Range
    Dim PetCells As Range
    Dim PetCount As Integer
    Dim PetIndex As Integer
    Dim ws As Worksheet

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        'Exit if selecting multiple cells or cell not in column C
        If Target.CountLarge > 1 Or Target.Column <> 3 Then Exit Sub
        
        'Stop updating screen for faster code, and stop processing events until this is done
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        'Find range of cells used for validation table
        Set AnimalListCells = Range("C2", Range("C1").End(xlDown))
        
        'Exit if cell changed is NOT inside range of validation table
        If Not Application.Intersect(AnimalListCells, Range(Target.Address)) Is Nothing Then
        
            NewValue = Target.Value 'Store new value of cell changed
            Application.Undo 'Set changed cell back to original value
            OldValue = Target.Value 'Store old value of cell changed
            Target.Value = NewValue 'Set changed cell back to new value

            'Find range and number of cells hold current "pets"
            Set ws = Worksheets("Sheet2")
            
            'Clear filter(s)
            'If ws.FilterMode = True Then
            '    ws.ShowAllData
            'End If
            
            'Code below misses replacing an item if it's the last one in the list
            'and if it's unchecked (filtered out)
            Set PetCells = ws.Range("A2", ws.Range("A1").End(xlDown))
            PetCount = PetCells.Count
        
            'Look through range of cells containing "pets" and change old values to new values
            PetCells.Replace What:=OldValue, Replacement:=NewValue, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        End If
        
        'Start updating screen and processing events again
        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End Sub

    Thursday, February 27, 2020 5:09 PM

All replies

  • I tried adding .Offset(1,0) to the Range of PetCells, and it now correctly replaces "dog" with "K9" with and without filtering "dog" from the list. Is there a better way to to this? 

    Thanks again!

    Set PetCells = ws.Range("A2", ws.Range("A1").End(xlDown).Offset(1, 0))


    Thursday, February 27, 2020 7:04 PM
  • My celebration was premature. If I uncheck more than 1 item on the pets column and then change one of the validation cells, not all of those items are replaced. I tried using Offset(20,0), and items lower in the pets list are still not replaced as expected. So I'm missing something. What's interesting is unchecking an item on the filter list for that column seems to simply shrink/hide the row for the filtered item. I can manually increase the row size and see the item appear again. That seems to indicate the rows are still there but just reduced in size to become not visible, so the range selection and count of items shouldn't be affected by the filter, but it apparently is. Am I stuck with just disabling all filters and forcing all the rows to become visible before I run the Range selection? 

    Thursday, February 27, 2020 7:31 PM
  • To:  Rick8181
    re:  wrong size

    'At the top of the Sub...
    Const DesiredSize As Long = 10

    'Then...
    'Replace...
    'Set PetCells = ws.Range("A2", ws.Range("A1").End(xlDown))
    'With...     
    Set PetCells = ws.Range("A2").Resize(DesiredSize, 1)

    'The above creates PetCells with 10 rows
    '---

    The free Excel workbook "Professional_Compare" is available at MediaFire.
    (compares every cell OR each row against all rows (two worksheets) - choice of compare type)
    Includes optional Clean Data utility
    Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Saturday, February 29, 2020 1:36 PM