• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

If on this sheet tab, delete it from another.

I need help extending a macro to do one more additional step.

In the sample file, I would to search the MasterList sheet tab for any red text that is in column C, meaning that the CatCode is no longer valid. (its date is < today).
If the values in column C is < today, take the value from column A in that row and delete its corresponding row from the TemplateLayOut sheet tab.
May have to reformat the MasterList sheet tab, so the row only shows onceā€¦   it can show the same CatCode multiple times.

The goal is to get all the out-of-date items off the TemplateLayOut sheet tab, using the data on the MasterList tab
1 Solution
This worked for me.
Public Sub ClearOutOld()
    Dim wksML As Worksheet
    Dim wksTLO As Worksheet
    Dim dicUnique As Object
    Dim rngML As Range
    Dim rngTLO As Range
    Dim rngCell As Range
    Dim vItem As Variant
    Dim lngRow As Long
    Set dicUnique = CreateObject("scripting.dictionary")
    Set wksML = Worksheets("MasterList")
    Set rngML = wksML.Range(wksML.Range("A2"), wksML.Range("A2").End(xlDown))
    For Each rngCell In rngML
        If CDate(Replace(rngCell.Offset(0, 2).Value, "EST", vbNullString)) < Date Then
            If dicUnique.exists(rngCell.Value) Then
                dicUnique.Add rngCell.Value, 1
            End If
        End If
    Set wksTLO = Worksheets("TemplateLayOut")
    Set rngTLO = wksTLO.Range("A5").End(xlDown)
    For lngRow = rngTLO.Row To 4 Step -1
        If dicUnique.exists(wksTLO.Cells(lngRow, 1).Value) Then
        End If
End Sub

Open in new window

A different approach from aikimark, based on formulas and filters, rather than dictionaries and loops. Nothing wrong with his approach, but I wrote this code so I might as well post it.


Sub macroClean()

Dim lLastRow As Long

lLastRow = Sheets("MasterList").Cells(Rows.Count, 1).End(xlUp).Row

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

With Sheets("TemplateLayOut")
    With .Range("T5:T" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        .FormulaR1C1 = _
            "=SUMPRODUCT((MasterList!R2C1:R" & lLastRow & "C1=TemplateLayOut!RC[-19])*1*(--(LEFT(MasterList!R2C3:R" & lLastRow & "C3,10))<TODAY()))"
        .Value = .Value
    End With
    With .Range("$A$4:T" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        .AutoFilter Field:=20, Criteria1:=">0"
    End With

End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub

Open in new window

How about this:
Sub Demo()

    Dim rngMasterList As Range, cMasterList As Range
    Dim rngTemplateLayOut As Range, cTemplateLayOut As Range
    Dim arrExpiredRows() As String
    Dim i As Integer
    Dim wsTemplateLayOut As Worksheet

    Set rngMasterList = Range("C2:C" & Range("C1048576").End(xlUp).Row)
    Set wsTemplateLayOut = Sheets("TemplateLayOut")
    Set rngTemplateLayOut = wsTemplateLayOut.Range("A5:A" & Range("A1048576").End(xlUp).Row)

    ReDim Preserve arrExpiredRows(0 To 1)
    For Each cMasterList In rngMasterList.Cells
        If CDate(Left(cMasterList.Value, 16)) < Now() Then
            ReDim Preserve arrExpiredRows(0 To UBound(arrExpiredRows) + 1)
            arrExpiredRows(UBound(arrExpiredRows)) = Range("A" & cMasterList.Row).Value
        End If

    For i = LBound(arrExpiredRows) To UBound(arrExpiredRows)
        For Each cTemplateLayOut In rngTemplateLayOut
            If cTemplateLayOut.Value = arrExpiredRows(i) Then
            End If

End Sub

Open in new window

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


I was originally going to create a criteria filter for the deletion, but the dictionary was quicker.  The ease-of-implementation was the non-datetime format of column C (Valid Until) data.

For future readers:
1. create a new worksheet
2. copy A:C from MasterList to the new worksheet
3. find/replace " EST" -> "" in the Valid Until cells in the new worksheet
4. Create a criteria range in H1:I2 in the new worksheet
Example using today's date
Cat Code	Valid Until

Open in new window

5. Apply an advanced filter to copy the unique values to E1 in the new worksheet with the criteria range from 4.
6. Apply an advanced filter in the TemplateLayOut worksheet, using the new_worksheet.range("E1"),newworksheet.range("E1").End(xldown) range
7. Delete the visible rows in the filtered TemplateLayOut worksheet.
8. Delete the newworksheet
Note: The first criteria range on the new worksheet can also be the one Valid Until column.  The Cat Code is superfluous in this algorithm description.
While it would be possible to have done the filtering in the MasterList sheet, I'm not comfortable offering solutions that mess with production data.
While my posted code iterates the cells of the MasterList, I could have transferred these values into a variant array in a single statement. Iterating the variant array is much faster than iterating the cells in a range.

I do this in my Better Concatenate Function article:

This is the inverse operation I describe in my Fast Data Push to Excel article:
RWayneHAuthor Commented:
Worked great for me too.  Thanks. for the help. EXCELent!!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now