Solved

If on this sheet tab, delete it from another.

Posted on 2014-04-03
8
196 Views
Last Modified: 2014-04-07
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
RemoveOutOfDateItems.xlsm
0
Comment
Question by:RWayneH
8 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39975474
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
            Else
                dicUnique.Add rngCell.Value, 1
            End If
        End If
    Next
    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
            wksTLO.Rows(lngRow).Delete
        End If
    Next
End Sub

Open in new window

0
 
LVL 39

Expert Comment

by:nutsch
ID: 39975484
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.

Thomas

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
        .AutoFilter Field:=20, Criteria1:=">0"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
    
    .Columns("T").Delete

End With

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


End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39975621
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)
    
    Sheets("MasterList").Activate
    
    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
    Next

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

End Sub

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 45

Expert Comment

by:aikimark
ID: 39975626
@Thomas

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
        	<4/3/2014

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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39975638
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39975651
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39975683
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:
http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html

This is the inverse operation I describe in my Fast Data Push to Excel article:
http://www.experts-exchange.com/A_2253.html
0
 

Author Closing Comment

by:RWayneH
ID: 39983075
Worked great for me too.  Thanks. for the help. EXCELent!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question