rearrange numerical data with eliminating -1 and 0

[amrezzat]

hi to all

i have the next column

-1
0
1
2
3
4
5

and continues up to about 300 entry
how can i eleminate the -1 and 0 numbers and rearrange the data again in the same column

thx in adavnce

[Henrich]

Hi, try this:

Sub Row_Deleting()
Row = 1
Do Until Cells(Row, 1) = ""
Value = Cells(Row, 1)
If Value = "-1" Or Value = "0" Then GoTo line1 Else GoTo line2
line1:
Rows("" & Row & ":" & Row & "").Select
Selection.Delete Shift:=xlUp
GoTo line3
line2:
Row = Row + 1
line3:
Loop
End Sub


„amrezzat" napÃ*sal (napÃ*sala):

>
> hi to all
>
> i have the next column
>
> -1
> 0
> 1
> 2
> 3
> 4
> 5
>
> and continues up to about 300 entry
> how can i eleminate the -1 and 0 numbers and rearrange the data again
> in the same column
>
> thx in adavnce
>
>
> --
> amrezzat
> ------------------------------------------------------------------------
> amrezzat's Profile: http://www.excelforum.com/member.php...o&userid=28766
> View this thread: http://www.excelforum.com/showthread...hreadid=521685
>
>

[amrezzat]

now you make me delete the complete row
and i updated it to delete the only cell in the column which contains -1 or 0
but when i delete it
any other cell have a reference to it make an error #ref
like the follwoing


a b
1 -1 =a1=-1
2 0 =a2=0
3 1 =a3=1
4 2 =a4=2
5 3 =a5=3

but after i delete it by that code
----------------------------------------
row = 4
Do Until Cells(row, "a") = ""
value = Cells(row, "a")
If value < 1 Then GoTo line1 Else GoTo line2
line1:

Cells(row, "a").Select
'Rows("" & Row & ":" & Row & "").Select

Selection.Delete Shift:=xlUp
GoTo line3
line2:
row = row + 1
line3:
Loop
--------------------------------------------------

a b
1 1 #ref
2 2 #ref
3 3 1
4 2
5 3


how to fix that error

and make the b column be well referenced and show 1
2
3

[Henrich]

Hi, You didn't say that you want to delete only the cells. But try this - it
is deleting only the cells not rows (in my code it is the first column)

Sub Cells_Deleting()
Row = 1
Do Until Cells(Row, 1) = ""
Value = Cells(Row, 1)
If Value = "-1" Or Value = "0" Then GoTo line1 Else GoTo line2
line1:
Cells(Row, 1).Select
Selection.Delete Shift:=xlUp
GoTo line3
line2:
Row = Row + 1
line3:
Loop
End Sub




"amrezzat" wrote:

>
> now you make me delete the complete row
> and i updated it to delete the only cell in the column which contains
> -1 or 0
> but when i delete it
> any other cell have a reference to it make an error #ref
> like the follwoing
>
>
> a b
> 1 -1 =a1=-1
> 2 0 =a2=0
> 3 1 =a3=1
> 4 2 =a4=2
> 5 3 =a5=3
>
> but after i delete it by that code
> ----------------------------------------
> row = 4
> Do Until Cells(row, "a") = ""
> value = Cells(row, "a")
> If value < 1 Then GoTo line1 Else GoTo line2
> line1:
>
> Cells(row, "a").Select
> 'Rows("" & Row & ":" & Row & "").Select
>
> Selection.Delete Shift:=xlUp
> GoTo line3
> line2:
> row = row + 1
> line3:
> Loop
> --------------------------------------------------
>
> a b
> 1 1 #ref
> 2 2 #ref
> 3 3 1
> 4 2
> 5 3
>
>
> how to fix that error
>
> and make the b column be well referenced and show 1
> 2
> 3
>
>
> --
> amrezzat
> ------------------------------------------------------------------------
> amrezzat's Profile: http://www.excelforum.com/member.php...o&userid=28766
> View this thread: http://www.excelforum.com/showthread...hreadid=521685
>
>