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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,956
Members
430,330
Latest member
drAli77

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
Top