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

I believe the following code will do what you want, just adjust the range of
A1:F19 accordingly.

Sub RemoveTwo()

Dim r As Range
Dim l As Long

Set r = Range("A1:F19") 'Your range goes here

For l = 1 To r.Cells.Count
If r.Cells(l) = 2 Then r.Cells(l).Delete Shift:=xlUp
Next

Set r = Nothing

End Sub
--
Kevin Backmann


"pgc" wrote:

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

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

great thanks a lot
works a treat
Kevin B wrote:
I believe the following code will do what you want, just adjust the range of
A1:F19 accordingly.

Sub RemoveTwo()

Dim r As Range
Dim l As Long

Set r = Range("A1:F19") 'Your range goes here

For l = 1 To r.Cells.Count
If r.Cells(l) = 2 Then r.Cells(l).Delete Shift:=xlUp
Next

Set r = Nothing

End Sub
--
Kevin Backmann


"pgc" wrote:

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

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

Cheers Kevin
Kevin B wrote:
I believe the following code will do what you want, just adjust the range of
A1:F19 accordingly.

Sub RemoveTwo()

Dim r As Range
Dim l As Long

Set r = Range("A1:F19") 'Your range goes here

For l = 1 To r.Cells.Count
If r.Cells(l) = 2 Then r.Cells(l).Delete Shift:=xlUp
Next

Set r = Nothing

End Sub
--
Kevin Backmann


"pgc" wrote:

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

The biggest problem that you had was that you were walking through the whole
range, but only checking the activecell. So if activecell had the value 2,
the test passed for each cell in the range, and you cleared it.

You were also only testing column 1, so after fixing that, there was still a
problem.

--
HTH

Bob Phillips

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

"pgc" wrote in message
ups.com...
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