Remove Cells That Don't Begin with One of A List of Inputs

[joshmorris23x]

Hi all,

I'm looking for a macro, that when executed, would remove all cells that don't begin with one of 3 different

For example, if I had these cells in column A:

AA
AB
BC
CD
EA
FA

And I wanted to remove anything that didn't start with "A" OR "B" OR "C"
I would be left with just these cells:

EA
FA

Any and all help is appreciated
Thanks,
Josh

 

[mrshl9898]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

Not the shortest solution, still learning. Works though.


Sub removenonABC()


Dim rownum As Long
Dim sstring As String
Dim leftsstring As String


Application.ScreenUpdating = False


rownum = 2


Do Until Cells(rownum, 1).Value = ""
sstring = Cells(rownum, 1).Value
leftsstring = Left(sstring, 1)
If leftsstring = "A" Or leftsstring = "B" Or leftsstring = "C" Then
Else
Rows(rownum).Delete
rownum = rownum - 1
End If
rownum = rownum + 1
Loop


Application.ScreenUpdating = True


End Sub

 

[Peter_SSs]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

I wanted to remove anything that didn't start with "A" OR "B" OR "C"
I would be left with just these cells:

EA
FA

Josh, your red and blue statements are contradictory. If you wanted to end with just EA and FA you would need to remove rows that did start with A or B or C.

I have assumed that you want the blue result, the data is in column A with a heading in A1 and that column Z is free to use as a helper so that we can delete all the relevant rows at once.
If you want the red result then change this line in my code

rCrit.Cells(2).Formula = "=ISERROR(FIND(LEFT(" & .Cells(2).Address(0, 0) & ",1),""ABC""))"

Sub CheckFirstLetter()
  Dim rCrit As Range
  
  Set rCrit = Range("Z1:Z2")
  Application.ScreenUpdating = False
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    rCrit.Cells(2).Formula = "=FIND(LEFT(" & .Cells(2).Address(0, 0) & ",1),""ABC"")"
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    If .SpecialCells(xlVisible).Count > 1 Then .Offset(1).EntireRow.Delete
    If .Parent.FilterMode Then .Parent.ShowAllData
  End With
  rCrit.Cells(2).ClearContents
  Application.ScreenUpdating = True
End Sub

If your data was very large (many thousands of rows) then I would suggest a different method. Post back with details if that is the case (including approximate number of rows, what column the data is in, is there a heading, how many columns are used on your sheet etc).

 

[mrshl9898]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

Josh, your red and blue statements are contradictory. If you wanted to end with just EA and FA you would need to remove rows that did start with A or B or C.

Missed this, ignore my post. Apologies for not tagging also.

 

[joshmorris23x]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

Josh, your red and blue statements are contradictory. If you wanted to end with just EA and FA you would need to remove rows that did start with A or B or C.

I have assumed that you want the blue result]
[...]


If your data was very large (many thousands of rows) then I would suggest a different method. Post back with details if that is the case (including approximate number of rows, what column the data is in, is there a heading, how many columns are used on your sheet etc).

Hi Peter,

My mistake! You were right, I wanted the blue result.

My data is in fact very large, roughly 25,000 rows.
No header.
Everything in Column A1

I appreciate the help from previous posters, though I just received notification of a change to the input data.

There are timestamps at the beginning of the data, so now I need to remove any lines that:
Do not contain one of a few strings of characters at any point in it:
For example, if the list was:

ABCDE
FGHIJK
LMNOP
QRSTU

I would need something that could remove all cells that did not contain "GHI" OR "MNO"
resulting in

ABCDE
QRSTU


Thanks again!

 

[Peter_SSs]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

Missed this, ignore my post.

Your interpretation of the requirement may well be the right one!


Edit: ... or not - I've just seen Josh's latest post.

 

[Peter_SSs]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

For example, if the list was:

ABCDE
FGHIJK
LMNOP
QRSTU

I would need something that could remove all cells that did not contain "GHI" OR "MNO"
resulting in

ABCDE
QRSTU

1. Once again this is contradictory - you have removed the cells that did contain GHI or MNO !! Please clarify which is correct


2. Also, were you saying that the only column with any data is column A, or just that column A is the one to check but other columns might have other data?

 

[joshmorris23x]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

Argh, all this blue and red is confusing me haha.

Let me try this again.

ABCDE
FGHIJK
LMNOP
QRSTU

I would need something that could remove all cells that did not contain "GHI" OR "MNO"
resulting in

FGHIJK
LMNOP

The only column with data is column A.

 

[Peter_SSs]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

OK, thanks for the clarification.
One more: Is there really only two values to check ("GHI" and "MNO" in your example) or are there more? If more, approximately how many?

 

[joshmorris23x]

Re: How to Remove Cells That Don't Begin with One of A List of Inputs

There are more. I'd like to be able to change the # of strings, but there would be no more than 5 of them.
Also, they vary in length, and won't necessarily be the same # of characters.