Using Len with Left (or Mid) in VBA-CodePudding

I have a file that looks like this:

And what I need to do is to remove every individual "NA" from the range, using VBA. Without shifting cells.

I can't do (replace), because it's possible that there could be "na" in the other strings. I assume "Instr" won't work for the same reason.

I tried a couple methods, which I thought would work, but for the reasons I can't understand, they don't.

At first I tried Left Len, like this:

dim nb as workbook

for i = 2 to nb.sheets(1).cells(rows.count, 17).end(xlup).row

if left(range("Q" & i).value, 2, "NA", Len(range("Q" & i).value)) then
nb.sheets(1).range("Q" & i).value = ""

end if
next i

When that did not work, I tried this:

for i = 2 to nb.sheets(1).cells(rows.count, 17).end(xlup).row

if mid(range("Q" & i).value, 0   2, Len(range("Q" & i).value)) And _
Instr(1, (range"Q" & i).value), "NA") then
nb.sheets(1).range("Q" & i).value = ""

end if
next i

I also tried many other ways to rearrange Len, Left and Mid, but none worked.

And even though the solution doesn't have to be done using Left (mid) and Len, is there a way to do it this way? And if not, what other ways there are?

CodePudding user response：

You can use Replace if you specify Lookat:=xlWhole

With nb.Sheets(1)
    .Range("Q2:U" & .Cells(.Rows.Count,"Q").End(xlUp).Row) _
    .Replace "NA", "", Lookat:=xlWhole, MatchCase:=True
End With

CodePudding user response：

This updates each cell in the Range you specify.

Sub test()
    Dim c As Range
    For Each c In Range("A1:Z100")
        If c.Value = "NA" Then c.Value = ""
    Next
End Sub

But for something this simple, it makes more sense to just use the built-in Replace.