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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Cells in column N will fluctuate. Could sometimes be 10 cells while other times it could be 1000 cells.
 
Upvote 0
Hi - just want to follow up to see if there's anything that can be done for this case. Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top