URL clean

[Kamranas]

I need an excel VBA code that removes the following from my URL Column B, I need a dynamic range code, not a static range.
1 http://www.
2 https://www.
3 http://
4 https://
5 www.

more details
need an excel VBA code that removes from my URL
1 http://www.
2 https://www.
3 http://
4 https://
5 www.
use find and replace empty but the condition is this i need dynamic range.

 

[mikenelena]

Hi Kamranas,

Take a look at this code and see if it does what you want. Make a backup of your worksheet just in case, as actions done through VBA cannot be undone.

Sub RemoveURLPrefixes()
    Dim LastRow As Long
    Dim i As Long
    Dim URL As String
    Dim Prefixes() As String
    
    ' Define the prefixes to remove
    Prefixes = Split("http://www.,https://www.,http://,https://,www.", ",")
    
    ' Get the last used row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each cell in column B
    For i = 1 To LastRow
        ' Get the URL from the cell
        URL = Cells(i, "B").Value
        
        ' Remove the prefixes from the URL
        For Each Prefix In Prefixes
            If InStr(URL, Prefix) = 1 Then
                URL = Mid(URL, Len(Prefix) + 1)
            End If
        Next Prefix
        
        ' Update the cell with the cleaned URL
        Cells(i, "B").Value = URL
    Next i
End Sub

 

[Michael M]

How about

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 1 Step -1
If InStr(Cells(r, 2), "https://www.") Then Cells(r, 2) = Replace(Cells(r, 2), "https://www.", "")
Next r
End Sub

 

[Kamranas]

let me test this

 

[Kamranas]

let me test this

not working according to me

 

[Michael M]

Which code are you referring to ?

 

[Kamranas]

let me test this

you misses somethings

 

[Kamranas]

Hi Kamranas,

Take a look at this code and see if it does what you want. Make a backup of your worksheet just in case, as actions done through VBA cannot be undone.

Sub RemoveURLPrefixes()
    Dim LastRow As Long
    Dim i As Long
    Dim URL As String
    Dim Prefixes() As String
   
    ' Define the prefixes to remove
    Prefixes = Split("http://www.,https://www.,http://,https://,www.", ",")
   
    ' Get the last used row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
   
    ' Loop through each cell in column B
    For i = 1 To LastRow
        ' Get the URL from the cell
        URL = Cells(i, "B").Value
       
        ' Remove the prefixes from the URL
        For Each Prefix In Prefixes
            If InStr(URL, Prefix) = 1 Then
                URL = Mid(URL, Len(Prefix) + 1)
            End If
        Next Prefix
       
        ' Update the cell with the cleaned URL
        Cells(i, "B").Value = URL
    Next i
End Sub

Hi Kamranas,

Take a look at this code and see if it does what you want. Make a backup of your worksheet just in case, as actions done through VBA cannot be undone.

Sub RemoveURLPrefixes()
    Dim LastRow As Long
    Dim i As Long
    Dim URL As String
    Dim Prefixes() As String
   
    ' Define the prefixes to remove
    Prefixes = Split("http://www.,https://www.,http://,https://,www.", ",")
   
    ' Get the last used row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
   
    ' Loop through each cell in column B
    For i = 1 To LastRow
        ' Get the URL from the cell
        URL = Cells(i, "B").Value
       
        ' Remove the prefixes from the URL
        For Each Prefix In Prefixes
            If InStr(URL, Prefix) = 1 Then
                URL = Mid(URL, Len(Prefix) + 1)
            End If
        Next Prefix
       
        ' Update the cell with the cleaned URL
        Cells(i, "B").Value = URL
    Next i
End Sub

Thank you so much

 

[mikenelena]

Thank you so much

It's my pleasure! Glad it worked for you.