How to delete a cell that doesn't have an "a" or "b" at the end of the value?

[288enzo]

Hello,

I know how to use a formula such as =IF(RIGHT(C1,1)="a",TRUE,FALSE), but how do I go about incorporating that into VBA?

I'm trying to look at column D, a cell value that doesn't end with an "a" or "b" should be deleted. BTW, my data varies, and so I use LR quite a bit as can't assume the last row is always going to be the same.

Has_Has_Not_Taken_Report_04282022.xlsx.xls
	A	B	C	D	E
1	619155M5B	ASE	87	87	ASE
2	619155M6B	ASE	82	82a
3	619155M7B	ASE	83	83a
4	619156B	ASE	80	80
5	619158B	ASE	84	84a
6	VCIWB01SSFIA22	ASE	82	82b
7		ASE	83	83b
8		ASE	84	84b
Working

The result would look like -

Has_Has_Not_Taken_Report_04282022.xlsx.xls
	A	B	C	D	E
1	619155M5B	ASE	87		ASE
2	619155M6B	ASE	82	82a
3	619155M7B	ASE	83	83a
4	619156B	ASE	80
5	619158B	ASE	84	84a
6	VCIWB01SSFIA22	ASE	82	82b
7		ASE	83	83b
8		ASE	84	84b
Working

Thank you

 

[kevin9999]

There's a number of different ways you could do this with VBA, here's just one (assumes the code is run from the workbook with the data)

Option Explicit
Sub keep_AB()
    Dim ws As Worksheet, i As Long, a
    Set ws = Worksheets("Working")
    a = ws.Range("D1", ws.Cells(Rows.Count, "D").End(xlUp))
    
    For i = 1 To UBound(a)
        If Not a(i, 1) Like "*a" And Not a(i, 1) Like ("*b") Then _
        a(i, 1) = vbNullString
    Next i
    ws.Range("D1").Resize(UBound(a)).Value = a
End Sub

 

[288enzo]

There's a number of different ways you could do this with VBA, here's just one (assumes the code is run from the workbook with the data)

Option Explicit
Sub keep_AB()
    Dim ws As Worksheet, i As Long, a
    Set ws = Worksheets("Working")
    a = ws.Range("D1", ws.Cells(Rows.Count, "D").End(xlUp))
   
    For i = 1 To UBound(a)
        If Not a(i, 1) Like "*a" And Not a(i, 1) Like ("*b") Then _
        a(i, 1) = vbNullString
    Next i
    ws.Range("D1").Resize(UBound(a)).Value = a
End Sub

Worked perfectly, thank you. Now, I get to try and figure out what exactly is happening

 

[JEC]

One more variant

Sub jec()
Range("D1", Range("D" & Rows.Count).End(xlUp)).Name = "ar"
[ar] = [if((right(ar)<>"a")*(right(ar)<>"b"),"",ar)]
End Sub

Or (same idea):

Sub jec()
 With Range("D1", Range("D" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("if((right(#)<>""a"")*(right(#)<>""b""),"""",#)", "#", .Address))
 End With
End Sub

 

[kevin9999]

Worked perfectly, thank you. Now, I get to try and figure out what exactly is happening

Glad we could help and thanks for the feedback Personally, I would look at one of @JEC 's suggestions - the less code the better

 

[Peter_SSs]

the less code the better

I'm not suggesting anything at all is wrong with @JEC's codes here, but as a general rule I don't think that is a good way to judge a code.

I don't know how representative the original sample data is but based on that alone, it looks like an alternative approach would be "if the value is numeric then clear it". If that was the case then another along the lines of JEC's code might be

Sub ClearCells()
  With Range("D1", Range("D" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(#),"""",#)", "#", .Address))
  End With
End Sub

 

[bebo021999]

In general, to delete all non-value in column D:

Range("D:D").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

 

[kevin9999]

I'm not suggesting anything at all is wrong with @JEC's codes here, but as a general rule I don't think that is a good way to judge a code.

I don't know how representative the original sample data is but based on that alone, it looks like an alternative approach would be "if the value is numeric then clear it". If that was the case then another along the lines of JEC's code might be

Sub ClearCells()
  With Range("D1", Range("D" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(#),"""",#)", "#", .Address))
  End With
End Sub

Yes Peter, you're quite right

 

[Peter_SSs]

Range("D:D").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

Only with that, to avoid a possible code error, you would need to use an 'on error' or else first check that there are some such values to clear.