Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rearrange data | Excel Discussion (Misc queries) | |||
help me on rearrange cells based on its numerical values without repeating any number | Excel Programming | |||
help me on rearrange cells based on its numerical values without repeating any number | Excel Worksheet Functions | |||
How to rearrange data | Excel Programming | |||
how to rearrange data | Excel Programming |