RWayneH
asked on
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
RemoveOutOfDateItems.xlsm
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
@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
6. Apply an advanced filter in the TemplateLayOut worksheet, using the new_worksheet.range("E1"), newworkshe et.range(" E1").End(x ldown) range
7. Delete the visible rows in the filtered TemplateLayOut worksheet.
8. Delete the newworksheet
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
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"),
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:
https://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:
https://www.experts-exchange.com/A_2253.html
I do this in my Better Concatenate Function article:
https://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:
https://www.experts-exchange.com/A_2253.html
ASKER
Worked great for me too. Thanks. for the help. EXCELent!!
Thomas
Open in new window