1

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

Community
  • 1
  • 1
Vasileios Georgakopoulos
  • 13
  • 1
  • 1
  • 3
  • 1
    A) Yes. B) No you can use the same cells – CLR Aug 16 '17 at 14:03
  • 1
    To add to what @CLR said, declare B5:F50 a range, Loop through each cell in the range and, in each iteration, set the cell value = Left([Cell value],Len(trim([Cell Value]))-1) – Jeremy Aug 16 '17 at 14:06

2 Answers2

1

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
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14
0

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?

CLR
  • 11,284
  • 1
  • 11
  • 29