Backspace a cell using VBA

[Kaiser TCF]

hi,
fyi, I am using 2019 Excel


so in short. I want a VBA code that do this in excelsheet: "F2" -> "BackSpace"
(F2 enables editing a cell)

basically, I wrote a code that imports text into excel sheet cells. and I noticed that each cell have a new empty paragraph after the end of the text.

deleting the empty paraph is easy. just "F2" ->"Backspace". but I have to do it for hundreds of cells so thats why I need to write a code

thanks

 

[JEC]

Try this

Sub jec()
 Dim ar As Variant, i As Long
 ar = Range("A1", Range("A" & Rows.Count).End(xlUp))
 
 For i = 1 To UBound(ar)
    ar(i, 1) = Mid(ar(i, 1), 1, Len(ar(i, 1)) - 1)
 Next
 
 Range("A1", Range("A" & Rows.Count).End(xlUp)) = ar
End Sub

 

[Kaiser TCF]

Try this

Sub jec()
 Dim ar As Variant, i As Long
 ar = Range("A1", Range("A" & Rows.Count).End(xlUp))
 
 For i = 1 To UBound(ar)
    ar(i, 1) = Mid(ar(i, 1), 1, Len(ar(i, 1)) - 1)
 Next
 
 Range("A1", Range("A" & Rows.Count).End(xlUp)) = ar
End Sub

thanks! it works

 

[Peter_SSs]

Why not process all the cells at once?

If the extra character only occurs once in each cell then you should be able to just Find/Replace.
Would have to identify what that last character is (I have guessed CHAR(10) or Alt+Enter)

Columns("A").Replace What:=Chr(10), Replacement:="", LookAt:=xlPart

If the extra character may also appear earlier in the cell and you don't want it removed from there, or if you can't/don't want to identify that last character you can still do them all at once ..

With Range("A1", Range("A" & Rows.Count).End(xlUp))
  .Value = Evaluate(Replace("if(#="""","""",left(#,len(#)-1))", "#", .Address))
End With