Delete all cell content in column except for desired string

**SammieRae** · 04-23-2013, 01:15 PM

Hello all, having troubles here and can't find an answer to help in the forum.

I have a code here that I want to cycle through all values in column E. If the value in column E is anything other than 'Volunteer & Internship' I want to clear the cell content. I have tried various codes, but here is the last. I keep getting a mismatch error for this one and don't know where the mismatch is. Maybe it is more than just that error too, not sure. This is what I have, anyone able to correct me?

Sub Ministry_Info_Organization()

Dim p As String

Do Until Range("A:A").Value = ""
    
    p = sht.Range("E:E").String
    If p <> "Volunteer & Internship" Then
        cell.ClearContents
    End If
    
Loop  

End Sub

**tehneXus** · 04-23-2013, 01:51 PM

Hi,

try this:

Option Explicit

Sub Ministry_Info_Organization()
    Dim p As String
    Dim lngRow As Long
    With ActiveSheet
        For lngRow = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(lngRow, 1).Value <> "Volunteer & Internship" Then .Cells(lngRow, 1).Value = vbNullString
        Next lngRow
    End With
End Sub

Regards

**SammieRae** · 04-23-2013, 02:20 PM

Sorry, I don't really know if I understand what the code you offered is doing... It doesn't seem to specify anywhere that this should be done in Column E and that the cell content in column E only is to be cleared if it doesn't have 'Volunteer & Internship'. Could you please explain? Thanks.

**tehneXus** · 04-23-2013, 02:29 PM

Hi,

and that the cell content in column E only is to be cleared if it doesn't have 'Volunteer & Internship'

thats done here:

If .Cells(lngRow, 5).Value <> "Volunteer & Internship" Then .Cells(lngRow, 5).Value = vbNullString

It doesn't seem to specify anywhere that this should be done in Column E

That's my mistake, did it for column A, here is it for column E in the current activated worksheet
The column is specified as the index number of the column, 5 for E

Option Explicit

Sub Ministry_Info_Organization()
    Dim lngRow As Long
    With ActiveSheet
        For lngRow = 1 To .Cells(.Rows.Count, 5).End(xlUp).Row
            If .Cells(lngRow, 5).Value <> "Volunteer & Internship" Then .Cells(lngRow, 5).Value = vbNullString
        Next lngRow
    End With
End Sub

it's running from row 1 till the last filled row in column E

Regards

**SammieRae** · 04-23-2013, 05:12 PM

Thank you for explaining. I know understand what it is doing and got it to work!

**SammieRae** · 04-23-2013, 06:17 PM

Okay, so here is another question pertaining to this, because I can't seem to get it to work. Can you ask for it to look for two different strings 'volunteer & internship' or 'Adventure & Culture' and then clear any cells that do not have one or the other?

I put the following and now it clears everything. Why?

With ActiveSheet
    For lngRow = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
        If .Cells(lngRow, 5).Value <> "Volunteer & Internship" _
        Or .Cells(lngRow, 5).Value <> "Adventure & Culture" Then .Cells(lngRow, 5).Value = vbNullString
    Next lngRow
End With