VBA Code Question: Finding Series of Data & Removing It (Please read igold)

[DRomito]

The original thread got ALL messed up. Anywho attached is a screen of the data I'm working with. Below that is the code I've written along with your segment placed into it. I've updated yours to represent column B and it's running an infinite loop and not removing anything. Any suggestions?

Here is the code:

Sub Create_List2()
'
' Create_List2 Macro
'


'Copy Formula tab to List
    Sheets("List").Select
    Columns("A:A").ClearContents
    Sheets("Formula").Select
    Columns("A:A").Select
    Selection.Copy
    Sheets("List").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Find and Replace blanks
    Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Call Work_Off_This
    
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

Your snippet updated to column B:

Sub Work_Off_This()


    Dim dat1 As String
    Dim dat2 As String
    Dim lrow As Long, i As Long, x As Long
    
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 1 To lrow
        If Cells(i, 2) = "" Then GoTo Blnk
        dat1 = Cells(i, 2).Value
        If Cells(i + 1, 2).Value = "" Then GoTo Blnk
        dat2 = Cells(i + 1, 2).Value
            For x = i + 2 To lrow
                If Cells(x, 2).Value = dat1 And Cells(x + 1, 2) = dat2 Then
                    Cells(x, 2).ClearContents
                    Cells(x + 1, 2).ClearContents
                End If
            Next
Blnk:
  Next
  
End Sub

 

[igold]

I replied to your PM...

 

[igold]

Hard to say from what I am seeing. My first thought would to make sure that before you call my code you make sure that the activesheet is the sheet on which you want my code to run. I have set a definitive value for my For Next loop, so it should not be running infinitely. Alternatively you could just add additional code to my code to make the correct sheet the activesheet.

Additionally, I believe your code was written with the Macro Recorder. We could clean that up quite a bit, and it would a lot more efficient...

igold

 

[DRomito]

Alrighty... I was playing around with this and it appears to select the wrong points of data. In this instance I need it to select the "/tr" and the "tr expanded=true" and remove every instance of that. Thoughts?

We can worry about efficiency once this little bugger works.