How to delete first character using vba.

[ivan_fuzz]

I need to delete the first character in every cell that has data in it on my spreadsheet except for the header row. This formula works..... =RIGHT(A3,LEN(A3)-1)....but I have thousands of rows and multiple columns. A macro would be much easier. Does anyone know how to convert this formula into vba? Thanks.

 

[just_jon]

Got any 1-character cells? Got any leading spaces?

EDIT - WTH, I'll guess...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ShortStuff()
<SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Set</SPAN> r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> r
    i = Len(Trim(c.Value))
    <SPAN style="color:#00007F">If</SPAN> i > 1 <SPAN style="color:#00007F">Then</SPAN>
        c.Value = Right(Trim(c.Value), i - 1)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">Set</SPAN> r = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[erik.van.geit]

Hello, ivan_fuzz,

the formula is
Range("A3") = Right(Range("A3"), Len(Range("A3")) - 1)

to loop
for i = 1 to 111
cells(i,1) = Right(...

but there is better then looping through all cells separetely

Sub delete_first_character()
Columns(2).Insert
With Range(Cells(3, 1), Cells(65536, 1).End(xlUp))
.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]<>"""",RIGHT(RC[-1],LEN(RC[-1])-1),"""")"
.Offset(0, 1).Copy
.PasteSpecial Paste:=xlPasteValues
.Value = .Value
End With
Columns(2).Delete
End Sub

this works for cell A3 to the last used cell in column A
if there are no data in column 256 (IV) then you can loop this way through all the columns

Sub delete_first_character()
Dim lc As Integer
Dim col As Integer
lc = Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False, False).Column
For col = 1 To lc
Columns(col + 1).Insert
With Range(Cells(3, col), Cells(65536, col).End(xlUp))
.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]<>"""",RIGHT(RC[-1],LEN(RC[-1])-1),"""")"
.Offset(0, 1).Copy
.PasteSpecial Paste:=xlPasteValues
.Value = .Value
End With
Columns(col + 1).Delete
Next col

End Sub

you could also
insert a new page
fill the entire range at once with formulas
copy and then paste the values
delete the new page

kind regards,
Erik

 

[ivan_fuzz]

Thanks for all the help. I ended up modifying 'just_jon' code a little and got it to work. The first character I needed to remove was an apostrophe but I didn't want a find and replace. (way too slow) This code works really good.

Thanks again.

Sub RemoveFirstChar()

Dim c As Range
Application.ScreenUpdating = False
For Each c In Cells.SpecialCells(xlCellTypeConstants)
If Left(c, 1) = "'" Then
c.Value = Right(c, Len(c) - 1)
End If
Next c
Application.ScreenUpdating = True
End Sub