Solved

If on this sheet tab, delete it from another.

Posted on 2014-04-03
8
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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: 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.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 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