PDA

View Full Version : Solved: delete leter



oleg_v
01-18-2010, 01:00 AM
Hello

I need dome help!
I need to delete all the letters in the column "b" sheet2 starting with "b2"
and down


Thanks

Aussiebear
01-18-2010, 01:07 AM
Okay up till now I've been very tolerant of your posting style. Are you sure that you have posted the correct amount of information in relation to your question?

For example;
you said "I need to delete all the letters in the column "b" sheet2 starting with "b2"

I'm assuming that you need to delete all strings within column B which might start with 'b2" right?

Aussiebear
01-18-2010, 01:11 AM
And whilst you are thinking about the issue, when does this event occur ( On entry, as a Sub or Macro, on recalculation etc)

Remember you made a promise in a previous thread to supply the full info and a workbook

Bob Phillips
01-18-2010, 01:36 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "B").Value2 Like "b2*" Then

.Cells(i, "B").Value2 = ""
End If
Next i
End With

End Sub

oleg_v
01-18-2010, 01:57 AM
Hi
there is a file attached.
in sheet2 you can see in the column "B" numbers and letters in the same cell
what i need is to delete all the letters and "," in the column "b" starting in the cell "b2" and downwards.

thanks
i will try to explaim my self better
no hard feelings i hope!!!!


Oleg

Bob Phillips
01-18-2010, 02:06 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim pos As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRow

pos = InStr(.Cells(i, "B").Value2, ",")
If pos > 0 Then

.Cells(i, "B").Value2 = Left$(.Cells(i, "B").Value2, pos - 1)
End If
Next i
End With

End Sub

oleg_v
01-18-2010, 02:12 AM
Hi
it gives me an error "type miss match"

Aussiebear
01-18-2010, 03:08 AM
It seems to work for me. The data in Column B of Sheet 2 has been changed to 0.000 format

oleg_v
01-18-2010, 04:03 AM
Hi
I opened a new workbook and it is working perfect

Thanks a lot!!!