VBA Clearing Cell Contents if Multiple conditions are not me

uperrwg

New Member
Joined
Sep 19, 2014
Messages
4
Hello,
I'm trying to create a macro that will clear contents of cells if they do not meet multiple criteria.
For example,
Say column N is filled with various letters of the alphabet - 1 letter to each cell. I need help with a macro that would clear the contents of the cell if it is not equal to A, B, C or D. Note that column N would also have a header that should NOT be deleted.
Thank you.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
I just wrote this macro in another post.

Code:
Sub ClearCells()

For y = 14 To 14 Step 1 'CHANGE THIS FOR COLUMNS
    For x = Cells(Rows.count, y).End(xlUp) To 2 Step -1
        Select Case Cells(x, y).value
            Case "A", "B", "C", "D"
                'Cells(x, y).Clear
            Case Else
                Cell(x, y).Clear
        End Select
    Next x


End Sub
 

uperrwg

New Member
Joined
Sep 19, 2014
Messages
4
I'm getting a Run-time error '13': Type mismatch error on line 3: For x = Cells(Rows.Count, y).End(xlUp) To 2 Step -1 (P.S. I modified the cases a bit)

Sub ClearCells()
For y = 14 To 14 Step 1 'CHANGE THIS FOR COLUMNS
For x = Cells(Rows.Count, y).End(xlUp) To 2 Step -1
Select Case Cells(x, y).Value
Case "Associate", "Associate Counsel", "Attorney", "Consultant", "Contract Attorney", "Counsel", "Director", "Employee", "Hiring Partner", "IP Counsel", "Junior Partner", "Law Professor", "Legal Support", "Managing Partner", "Marketing Partner", "Member", "Of Counsel", "Officer", "Other", "Partner", "Principal", "Retired Partner", "Senior Associate", "Senior Attorney", "Senior Counsel", "Senior Partner", "Shareholder", "Solicitor", "Special Counsel", "Staff Attorney", ""
'Cells(x, y).Clear
Case Else
Cells(x, y).Clear
End Select
Next x
Next y

End Sub
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
I see you caught my other errors in excluding a next statement. I'm not sure why you're getting that error. It should return a long, and x is declared as a variant (because it isn't declared). How many cell are in column N?
 

uperrwg

New Member
Joined
Sep 19, 2014
Messages
4
Cells in column N will fluctuate. Could sometimes be 10 cells while other times it could be 1000 cells.
 

uperrwg

New Member
Joined
Sep 19, 2014
Messages
4
Hi - just want to follow up to see if there's anything that can be done for this case. Thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,993
Messages
5,526,114
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top