How to delete first character using vba.

ivan_fuzz

Board Regular
Joined
Jan 7, 2005
Messages
63
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
Code:
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

Board Regular
Joined
Jan 7, 2005
Messages
63
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. :p

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
 

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top