Solved

If on this sheet tab, delete it from another.

Posted on 2014-04-03
8
203 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 46

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 46

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 46

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 46

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 46

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

627 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