Filtered column with VBA coding

[Shark420]

I am new to VBA Coding. can any body tell me about how do i change the value of filtered column based on cell value of another filtered column in the same excel sheet.

 

[Monty]

Hello Shark
If you want to change the values in a filtered column based on the values in another column while both columns are filtered, you can use VBA to loop through the visible cells and make the changes. Here's an example assuming you have a column "ColumnA" and want to change values in "ColumnB" based on the values in "ColumnA":

Sub ChangeValuesBasedOnFilter()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim cellA As Range
    Dim cellB As Range


    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")


    ' Assuming your data starts from row 2 in columns A and B
    Set rngData = ws.Range("A2:B" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)


    ' Loop through visible cells in Column A
    For Each cellA In rngData.Columns(1).SpecialCells(xlCellTypeVisible)
        ' Check if Column B is filtered
        If Not ws.AutoFilterMode Then
            Exit Sub
        End If

        ' If Column B is visible and not hidden by the filter, change its value based on Column A
        If Not rngData.Columns(2).Cells(cellA.Row, 1).EntireRow.Hidden Then
            Set cellB = rngData.Columns(2).Cells(cellA.Row, 1)
            ' Your condition to change the value in Column B based on Column A
            If cellA.Value = "YourCondition" Then
                cellB.Value = "NewValue"
            End If
        End If
    Next cellA

    ' Clear the filter to show all data
    ws.AutoFilterMode = False
End Sub

Replace "Sheet1" with your actual sheet name, and adjust the column letters ("A" and "B") as needed.

This example checks each visible cell in Column A, and if the corresponding cell in Column B is visible, it changes the value based on a condition. Adjust the condition and the new value according to your specific requirements.