Thanks Bob
works well
dont suppose you could tell me in plain english why my code didnt work
cheers paul
Bob Phillips wrote:
Sub RemoveCells_ShiftUp()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Rows.Count + 1 To 2 Step -1
For iCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
If Cells(rw - 1, iCol).Value = 2 Then
Cells(rw - 1, iCol).Delete Shift:=xlShiftUp
End If
Next iCol
Next rw
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"pgc" wrote in message
ups.com...
Please help i have a sheet like this
A b c
2 fun 1
run 2 100
i want to delete the numer2 in column A and move "run" in column A to
cell A1, same in column B,C etc
i have started a code but it deletes all cells not just the ones with
the number 2 in
heres my code

Sub RemoveCells_ShiftUp()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If ActiveCell.Value = 2 Then _
Cells(rw, 1).Delete Shift:=xlShiftUp
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
by the way i am not very good with code
Hope someone can help