Replace cells in another column after filtering

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
32
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everybody!

So, I'd like to create a macro that will be very useful in my tasks, he he. For this to work, I need to filter a specific value and then the cells of the same rows, but in another column they will be replaced by the value I use in the code. Of course, I accept other suggestions for how I can accomplish this task : - D. That's all, I'll wait and thank you for your attention. Follow the example below:

VBA Code:
Sub FilterA_and_ReplaceB()
  
    Dim W1 As Worksheet
    Dim rowNumber As Long
    
    Set W1 = Sheets("Sheet1")
    W1.Select
    rowNumber = 2    
    
   ''' The While and Do Until structures will iterate row by row of the table until they find an empty cell
    
   While (W1.Cells(rowNumber, "A").Value <> "")
        
            Do Until (W1.Cells(rowNumber, "A").Value = "")
            rowNumber = rowNumber + 1
            Loop

   ''' If the value of a cell in column "A" matches the search criteria, that value is filtered

            If (W1.Cells(rowNumber, "A").Value = "Example") Then
            W1.Cells.Range("A1").Select
            ActiveSheet.Range("$A$1:$B$1048576").AutoFilter Field:=1, Criteria1:="Example"

   '''Once the filter is done, this is where I should insert a code that marks column "B" starting from line 2. 
   '''The contents of the cells in column "B" will be replaced and the filter selection will be deselected. All of this in a dynamic way.

            W1.ShowAllData
        W1.Cells(1, 1).Select
            MsgBox ("The cells in column B were successfully replaced.")
            Exit Sub

        Else
        Msgbox ("The cells in column B were already replaced!")
    
            End If         
    Wend
    
End Sub
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
What value do you want to put in col B?
 
Upvote 0
What value do you want to put in col B?
Thanks for responding.

The value of column B will correspond to the one selected in column A. To give a better example, let's imagine that column A stores names and B surnames. Filtering the "Walter" values in A, I want the corresponding lines in column B to receive "White", whether they are filled or empty.
 
Upvote 0
Ok, how about
VBA Code:
Sub F_Ribeiro()
   Dim UsdRws As Long
   
   With Sheets("Sheet1")
      UsdRws = Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:B1").AutoFilter 1, "Walter"
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Columns(2).Resize(UsdRws - 1).Value = "White"
      End If
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top