3 Seasons only

[nitebyfly]

I have 21K rows of data where I need to delete entries under the following criteria;

delete those rows that only match one season OR
delete those rows that only match two seasons.

Keep those rows that where a name matches three seasons
(Fall, Jan(winter), Spring)

A B C
1 last first season
2 smith james fall
3 smith james jan(winter)
4 smith james spring
5 taylor john fall
6 taylor john spring
7 sand fresh fall

In this example the goal would be to delete or mark for deletion rows 5, 6 & 7.

Thank you for any help you can provide.

Tim

 

[texasalynn]

try this

Sub Seasons()
Dim LR As Long, i As Long
Dim Season As String
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
Season = Cells(i, 3)
Select Case Season
Case "fall"
    'keep row
Case Is = "spring"
    'keep row
Case Is = "jan(winter)"
    'keep row
Case Else
    Rows(i).EntireRow.Delete
End Select
Next
MsgBox "Done"
End Sub

 

[nitebyfly]

Thank you for your help. When I put your code in VBA and run it, it removes them all except for the heading. Could you possible look at it again?
Thanks so much.

Tim

 

[texasalynn]

perhaps your data is not exactly those words? Had spaces or is not the same case (upper/lower) that would cause the rows to be deleted

 

[texasalynn]

change this to add the LCase

Season = LCase(Cells(i, 3))

 

[nitebyfly]

Thank you again for following this. I copied the example from my message, then Text to columns, inserted your code in vba, then ran it - and away it went deleting all but the heading row. It is true on the one I am planning on using it for, that the cases are different. If you wrote it for the example I gave, if it was going to work, wouldn't by copying it and setting it up in columns, without changing any case, then running your code be the test?

Again, my humble thanks.


Tim

 

[nitebyfly]

Tried the change - this time it took the header also.

Tim

 

[texasalynn]

ok so before running by code i have

Excel Workbook
	A	B	C	D
1	last	first	season	1
2	smith	james	fall	2
3	smith	james	jan(winter)	3
4	smith	james	spring	4
5	taylor	john	fall	5
6	taylor	john	spring	6
7	sand	fresh	fall	7
8	taylor	john	summer	8
9	smith	james	summer	9
10	smith	james	Fall	10
11	smith	james	Spring	11
Sheet1

Excel 2003

after running code I have

Excel Workbook
	A	B	C	D
1	last	first	season	1
2	smith	james	fall	2
3	smith	james	jan(winter)	3
4	smith	james	spring	4
5	taylor	john	fall	5
6	taylor	john	spring	6
7	sand	fresh	fall	7
8	smith	james	Fall	10
9	smith	james	Spring	11
Sheet1

Excel 2003

 

[texasalynn]

Tried the change - this time it took the header also.

Tim

see my previous post for the before and after

is your test with the data header in row 1? Because my code stops at row 2

 

[nitebyfly]

Thank you for your trouble. I will give you some more background. This data is test data where students took an exam in the fall, then winter, then spring. The only students I want to look at are those that took all three, and delete the students that only took one or two of the three tests. So in my example, rows 5, 6, & 7 need to be deleted as they did not take 3 tests.
Does that help your analysis?

Tim