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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
>