is there a way with a macro to delete contents (text) of some cells in a
column, and all other adjacent cells move over one column to the left ? eg:
H I J K L M N O P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
m -1 90 G R 5 54 43 12.24
g -2 85 H T 5 54 43 12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

should be as follows:

H I J K L M N O P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
-1 90 G R 5 54 43 12.24
-2 85 H T 5 54 43 12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

down to 300 rows . the text in some cells are in random rows but always in
column H
i'm not familiar with VBA and i use excel 2000

thank you all
regards bill gras


--
bill gras

Bill

This macro will do it:

Sub Macro1()
For Each C In ActiveSheet.UsedRange.Columns("H").Cells
If Not IsNumeric(C.Value) Then
C.Delete Shift:=xlToLeft
End If
Next
End Sub

Disclaimer: I take no responsibility for lost data.
Complaint form: . (Try to fit any complaints in the black area!)

"bill gras" wrote in message
...
is there a way with a macro to delete contents (text) of some cells in a
column, and all other adjacent cells move over one column to the left ?
eg:
H I J K L M N O
P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
m -1 90 G R 5 54 43 12.24
g -2 85 H T 5 54 43
12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

should be as follows:

H I J K L M N O
P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
-1 90 G R 5 54 43 12.24
-2 85 H T 5 54 43 12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

down to 300 rows . the text in some cells are in random rows but always in
column H
i'm not familiar with VBA and i use excel 2000

thank you all
regards bill gras


--
bill gras

Hi Bill,

Try:

'==============
Public Sub Tester02()
Dim Rng As Range
Dim LRow As Long

LRow = Cells(Rows.Count, "H").End(xlUp).Row

Set Rng = Range("H2:H" & LRow)

On Error Resume Next
Rng.SpecialCells(xlCellTypeConstants, 2). _
Delete Shift:=xlToLeft
On Error GoTo 0
End Sub

'==============


---
Regards,
Norman



"bill gras" wrote in message
...
is there a way with a macro to delete contents (text) of some cells in a
column, and all other adjacent cells move over one column to the left ?
eg:
H I J K L M N O
P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
m -1 90 G R 5 54 43 12.24
g -2 85 H T 5 54 43
12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

should be as follows:

H I J K L M N O
P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
-1 90 G R 5 54 43 12.24
-2 85 H T 5 54 43 12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

down to 300 rows . the text in some cells are in random rows but always in
column H
i'm not familiar with VBA and i use excel 2000

thank you all
regards bill gras


--
bill gras

Hi Bill James and Norman Jones
Thank You for your swift reply and your help
regards Bill Gras
--
bill gras


"Norman Jones" wrote:

Hi Bill,

Try:

'==============
Public Sub Tester02()
Dim Rng As Range
Dim LRow As Long

LRow = Cells(Rows.Count, "H").End(xlUp).Row

Set Rng = Range("H2:H" & LRow)

On Error Resume Next
Rng.SpecialCells(xlCellTypeConstants, 2). _
Delete Shift:=xlToLeft
On Error GoTo 0
End Sub

'==============


---
Regards,
Norman



"bill gras" wrote in message
...
is there a way with a macro to delete contents (text) of some cells in a
column, and all other adjacent cells move over one column to the left ?
eg:
H I J K L M N O
P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
m -1 90 G R 5 54 43 12.24
g -2 85 H T 5 54 43
12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

should be as follows:

H I J K L M N O
P
-1 40 D H 5 54 43 32.64
-6 22 G R 5 54 43 25.60
-1 90 G R 5 54 43 12.24
-2 85 H T 5 54 43 12.04
-3 80 H C 5 54 43 16.86
-6 34 G K 5 54 43 18.51

down to 300 rows . the text in some cells are in random rows but always in
column H
i'm not familiar with VBA and i use excel 2000

thank you all
regards bill gras


--
bill gras