rearrange numerical data with eliminating -1 and 0
 

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

rearrange numerical data with eliminating -1 and 0
 

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

rearrange numerical data with eliminating -1 and 0
 

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

rearrange numerical data with eliminating -1 and 0
 

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