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

Answer 1

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