Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Delete question
I need to group records by the value in column A (employees, who can have
multiple records) and then delete all of any employees who have no records with a date in column D. Have given this some thought and have tried filters, loops, offsets and just can't quite figure out the best way to do it. The groups of records will be variable each week. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Delete question
Hi,
If I've understood correctly you want to read down column A which is a list of names and if a name doesn't have a date in column D then delete the row. If that's correct then right click your sheet tab, biew code and paste this in and run it Sub Stantial() Dim MyRange, MyRange1 As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If Not IsDate(c.Offset(, 3).Value) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "jeremiah" wrote: I need to group records by the value in column A (employees, who can have multiple records) and then delete all of any employees who have no records with a date in column D. Have given this some thought and have tried filters, loops, offsets and just can't quite figure out the best way to do it. The groups of records will be variable each week. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Delete question
Not quite, but much closer than I had made it.
1st 4 columns from my table are below - Employee A has records in January and February with subtotals and a grand total associated, but Employee B just started this week, has no data from January and nothing in February until the end of this week. I do not want his name even showing up in this table until they have completed their first full week. I need to be able to delete all records for Employee B out of this table so that a copy macro used later does not automatically create a seperate worksheet for that person. Employee A Shift 2 RFT GOALS Employee A Shift 2 RFT 1/17/2009 Employee A Shift 2 RFT 1/31/2009 Employee A Shift 2 RFT January Employee A Shift 2 RFT 2/7/2009 Employee A Shift 2 RFT February Employee A Shift 2 RFT Year To Date Employee B Shift 1 Seasonal GOALS Employee B Shift 1 Seasonal Employee B Shift 1 Seasonal Week Ending: Employee B Shift 1 Seasonal Employee B Shift 1 Seasonal February Employee B Shift 1 Seasonal Year To Date "Mike H" wrote: Hi, If I've understood correctly you want to read down column A which is a list of names and if a name doesn't have a date in column D then delete the row. If that's correct then right click your sheet tab, biew code and paste this in and run it Sub Stantial() Dim MyRange, MyRange1 As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If Not IsDate(c.Offset(, 3).Value) Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub Mike "jeremiah" wrote: I need to group records by the value in column A (employees, who can have multiple records) and then delete all of any employees who have no records with a date in column D. Have given this some thought and have tried filters, loops, offsets and just can't quite figure out the best way to do it. The groups of records will be variable each week. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Delete question
Jeremiah,
Looping is the way to go. BUT you work from the bottom of the list up. For instance you have a nice list with a number of columns, starting in A1. Sub DeleteRows() Dim rng As Range Dim lRows As Long Dim lCounter As Long Set rng = Range("A1").CurrentRegion lRows = rng.Rows.Count 'Loop through list from the bottom. For lCounter = lRows To 1 Step -1 If Cells(lCounter, 4).Value = "" Then Cells(lCounter, 4).EntireRow.Delete End If Next End Sub Make sure your list has NO entirely blank rows within it to begin with. Paul "jeremiah" wrote in message ... I need to group records by the value in column A (employees, who can have multiple records) and then delete all of any employees who have no records with a date in column D. Have given this some thought and have tried filters, loops, offsets and just can't quite figure out the best way to do it. The groups of records will be variable each week. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range - Delete question | Excel Programming | |||
delete macro question | Excel Programming | |||
A Marco Delete Question | Excel Programming | |||
help: automated delete without question | Excel Programming |