Only need every second number
 

I have a problem.
I have 2000 numbers in a colum, and every second number is zero. I nee
the numbers in between zero. How do i put these numbers in only on
culumb without zero

--
peterm
-----------------------------------------------------------------------
petermh's Profile: http://www.excelforum.com/member.php...fo&userid=2792
View this thread: http://www.excelforum.com/showthread.php?threadid=47423

Only need every second number
 

lastrow = cells(rows.count,"A").End(xlup).row
for i = lastrow to 1 step -1
if cells(i,1).Value = 0 then
cells(i,1).Delete Shift:=xlShiftUp
end if
Next

--
Regards,
Tom Ogilvy

"petermh" wrote in
message ...

I have a problem.
I have 2000 numbers in a colum, and every second number is zero. I need
the numbers in between zero. How do i put these numbers in only one
culumb without zero?


--
petermh
------------------------------------------------------------------------
petermh's Profile:
http://www.excelforum.com/member.php...o&userid=27927
View this thread: http://www.excelforum.com/showthread...hreadid=474232

Only need every second number
 

Here's a quicky simple way just put this together not entirely sure what
your trying to do as zero is counted as anything but the code below will
put the numbers which arent 0 in the column next to it just by simple
offset. I put this in the worksheet code as Activate just so i could
get it to work by clicking another sheet and clicking back but you can
create a keyboard shortcut or Auto_open or whatever you like!

HTH

P.S the last bit of code at the bottom (Cleanup) stick it a module on
its own!
Simon.

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range("A1:A10")
For Each mycell In rng
If mycell = 0 Then
ElseIf mycell < 0 Then
mycell.Offset(0, 1) = mycell.Value
End If
Next
Call Cleanup
End Sub

Sub Cleanup()
Dim rng As Range
Set rng = Range("a1:a10")
For Each mycell In rng
If mycell.Offset(0, 1) = "" Then
mycell.Offset(0, 1).Delete
End If
Next
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=474232

Only need every second number
 

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If Cells(i, 1).Value < 0 Then
x = x + 1
Cells(x, 2) = Cells(i, 1).Value
End If
Next i



"Tom Ogilvy" wrote:

lastrow = cells(rows.count,"A").End(xlup).row
for i = lastrow to 1 step -1
if cells(i,1).Value = 0 then
cells(i,1).Delete Shift:=xlShiftUp
end if
Next

--
Regards,
Tom Ogilvy

"petermh" wrote in
message ...

I have a problem.
I have 2000 numbers in a colum, and every second number is zero. I need
the numbers in between zero. How do i put these numbers in only one
culumb without zero?


--
petermh
------------------------------------------------------------------------
petermh's Profile:
http://www.excelforum.com/member.php...o&userid=27927
View this thread: http://www.excelforum.com/showthread...hreadid=474232