VBA Help How to transfer column to an array in order to search and replace as quick as possible

[Tardisgx]

Below is a macro that stores the search items in an array and replaces with nothing. I couldn't include <P style*> in this array because it is replaced with a value.

Unfortunately this bumps the completion time back up to how long it takes with a manually recorded macro. It was suggested to me to put the column in an array and 'work on that' I do not know how to do that; any help is appreciated. I'm unsure why my tagged code is previewing weirdly sorry.

Sub recreate()
Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
  StartTime = Timer
  
    Sheets("Columns").Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="<p style*>", Replacement:="<p>", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
  
  
    Dim Ar(1 To 17) As String '~~> 4 to number of items
    Dim i As Long
    Dim Ar2(1) As String '~~> 1 to number of items
    Dim a As Long
    Ar(1) = "<span style*>"
    Ar(2) = "<div>"
    Ar(3) = "<div style=*>"
    Ar(4) = "<tbody>"
    Ar(5) = "</div>"
    Ar(6) = "<ul style=*>"
    Ar(7) = "<li style=*>"
    Ar(8) = "<table style*>"
    Ar(9) = "<col style*>"
    Ar(10) = "<tr style=*>"
    Ar(11) = "<td class=*>"
    Ar(12) = "<colgroup>"
    Ar(13) = "</colgroup>"
    Ar(14) = "</tbody>"
    Ar(15) = "</td>"
    Ar(16) = "</tr>"
    Ar(17) = "</table>"

    For i = 1 To UBound(Ar)
        Sheets("Columns").Columns(6).Replace What:=Ar(i), _
                                             Replacement:="", _
                                             LookAt:=xlPart, _
                                             SearchOrder:=xlByRows, _
                                             MatchCase:=False, _
                                             SearchFormat:=False, _
                                             ReplaceFormat:=False
    Next i
    'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

 

[mse330]

I am not really sure I understood your request exactly but I just put up an example of what I think you are trying to achieve. I have put lots of comments for you to understand & follow. Hope that helps & if you have any further questions, post back again here

Sub recreate()

Dim StartTime As Double, SecondsElapsed As Double
Dim Ar() As Variant 'You don't need to specify the array size if you don't know it

'Remember time when macro starts
StartTime = Timer

'The below line copies the data (store values) from the worksheet to the array
'F2 where it starts & the rest is a dynamic way to find last row
Ar = Sheets("Columns").Range("F2", Range("F" & Rows.Count).End(xlUp)).Value

'You can change the values stored in the array by looping through all items using lower bound (LBound) & upper bound (UBound)
For i = LBound(Ar) To UBound(Ar)
'This is just to demonstrate how to check the values in the array
'If you think of the array as a grid of rows & columns so we can read its values as follows
'We use Ar(i,1) where the i refers to the rows & 1 refers to the columns of your array
    If Ar(i, 1) = "xyz" Then
        Ar(i, 1) = ""
    End If
Next I

'Now once you've made the necessary changes in the array you can copy the data back in the sheet as shown below
'The resize takes care of the range to copy the data in if it is more than 1 cell
Sheets("Columns").Range("F2").Resize(UBound(Ar)) = Ar
  
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

 

[Tardisgx]

What i'm replacing are html tags and ive released they are not appearing in my post here is a picture of code https://imgur.com/a/eRS3a8b above the array code you can see a normally recording find and replace because it the only thing I am not replacing with nothing.

Please tell me if this makes it clearer what I want to achieve

 

[mse330]

Try the below code & see if it does what you need. You need to create a new sheets called "HTML_Tags" & place all the tags that you wish to replace in column A

Sub Replace_HTML_Tags()
Dim DataArr() As Variant, TagsArr() As Variant, Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Sheets("HTML_Tags")
Set Ws2 = Sheets("Columns")
DataArr = Ws2.Range("F1", Ws2.Range("F" & Rows.Count).End(xlUp)).Value
TagsArr = Ws1.Range("A1", Ws1.Range("A" & Rows.Count).End(xlUp)).Value
For i = LBound(DataArr) To UBound(DataArr)
    For y = LBound(TagsArr) To UBound(TagsArr)
        If DataArr(i, 1) Like "*" & TagsArr(y, 1) & "*" Then DataArr(i, 1) = Replace(DataArr(i, 1), TagsArr(y, 1), "")
    Next y
Next i
Ws2.Range("F1").Resize(UBound(DataArr)) = DataArr
End Sub