Replace cells in another column after filtering

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
What value do you want to put in col B?
 

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,680
Messages
5,637,749
Members
416,982
Latest member
lisam77

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
Top