Find and replace using VBA

Question

I have a chart where I need to remove certain keywords from column C- Range C3:C5000(some cells are blank). The words that needs to be removed are placed in column A- Range A3:A100(some cells are blank). Both ranges gets changed for different files. I have written a code but its not working for dynamic range. Also I want to sort column c according to no. of characters in cell in Ascending order. please help

Sub Replace_Char()

Dim i As Integer
Dim Mpp As String

For i = 3 To 50
    Mpp = Cells(i, 1).Value
    If Cells(i, 1).Value <> 0 Then

Worksheets("Sheet1").Columns("C").Replace _
 What:=Mpp, Replacement:="", _
 SearchOrder:=xlByColumns, MatchCase:=True
 End If
 
Next i


End Sub

Nick Abbot · Accepted Answer · 2023-01-25 13:24:31Z

0

For dynamic ranges, you can try using the .UsedRange property. As for sorting by number of characters, create a column that has the formula like "=LEN(D1)" and then sort the sheet on that column.

Sub Replace_Char()

Dim i As Integer
Dim Mpp As String

'For i = 3 To 50
Dim Thing As Range
For Each Thing In ActiveSheet.UsedRange.Columns(1).Cells
    
    Mpp = Cells(i, 1).Value
    If Cells(i, 1).Value <> 0 Then

        Worksheets("Sheet1").Columns("C").Replace _
            What:=Mpp, Replacement:="", _
            SearchOrder:=xlByColumns, MatchCase:=True
    End If
 
'Next i
Next

End Sub

answered Jan 25, 2023 at 13:24

Nick Abbot

5011 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find and replace using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related