in this example below I want to remove the "B" using VBA. Thus, I download these values which spam from B5:F50 and all in the end have "B". My question is twofold. A) Is there a way to delete the "B" using VBA and replace these values in the same cells? B) Or have to be in new cells?
77.65B 86.73B 92.97B 84.7B 89.4B
The code would be like this
Sub test()
Dim rngDB As Range, vDB
Dim s As String, i As Integer, j As Integer
Set rngDB = Range("b5:f50")
vDB = rngDB
r = UBound(vDB, 1)
c = UBound(vDB, 2)
For i = 1 To r
For j = 1 To c
s = vDB(i, j)
s = Left(s, Len(s) - 1)
vDB(i, j) = s
Next j
Next i
rngDB = vDB
End Sub
Or
Sub test2()
Dim rngDB As Range
Set rngDB = Range("b5:f50")
rngDB.Replace "B", "", lookat:=xlPart
End Sub
The easiest (shortest) way I can see to do this is with a Search/Replace, but this would be on the assumption that all cells end in B and a B doesn't legitimately occur elsewhere in the cells.
Sheets("YourSheetNameHere").Range("B5:F50").Replace What:="B", Replacement:="", LookAt:=xlPart
Alternatively, you could loop through each cell and RIGHT/LEN etc. but why bother?
