Copy non-Zero columns to new location macro
 

I need a macro to run down a column A and where its value is 0 I need
to copy two columns to a new location.

----a----b----c----d
1---6---a
2---0---b
3---4---c

In this case I need to copy "6" to C1 and "a" to D1. Then copy "4" to
C2 and "c" to D2. There are up to five hundred rows.

Thanks for helping this thick headed sole.

Craig


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=379709

Copy non-Zero columns to new location macro
 

Give this a try


Code:
--------------------
Sub MoveNonZero()
Dim y As Long, x As Range
y = 1
For Each x In Selection
If x = 0 Then
Else
Range("C" & y) = x.Value
Range("D" & y) = x.Offset(0, 1).Value
y = y + 1
End If
Next x
End Sub
--------------------


Select the range or column you want to operate on and then run the
marco.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379709

Copy non-Zero columns to new location macro
 

Thanks for the quick reply.

This does not move the zeros. But it does copy column B to column C in
error when a zero is present in column A.

This is very close. If there is a zero in column A I want to skip the
whole row.

Thanks for your help.

Craig


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=379709

Copy non-Zero columns to new location macro
 

My Bad, I got the code wrong. This worked perfect! Thanks for th
OUTSTANDING help

--
Craig
-----------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438
View this thread: http://www.excelforum.com/showthread.php?threadid=37970

Copy non-Zero columns to new location macro
 

For the posted code to work Column A must be manually selected.

How do I focus this code on say a range from AC3 to AC75? So that th
code will run without having to select the data? :cool

--
Craig
-----------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438
View this thread: http://www.excelforum.com/showthread.php?threadid=37970

Copy non-Zero columns to new location macro
 

You can hard code the range to operate on either of these two ways. I'm
assuming that you want the results offset by 2 columns into AE and AF.
You can modify the destination ranges as needed.


Code:
--------------------
Sub MoveNonZero()
Dim y As Long, x As Range
y = 3
For Each x In Range("AC3:AC75")
If x = 0 Then
Else
Range("AE" & y) = x.Value
Range("AF" & y) = x.Offset(0, 1).Value
y = y + 1
End If
Next x
End Sub


Sub MoveNonZero2()
Dim y As Long, x As Long
y = 3
For x = 3 To 75
If Range("AC" & x) = 0 Then
Else
Range("AE" & y) = Range("AC" & x)
Range("AF" & y) = Range("AD" & x).Value
y = y + 1
End If
Next x
End Sub
--------------------


Take your pick

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379709

Copy non-Zero columns to new location macro
 

Your timely help is amazing. I have struggled with this all day.

Thank YOU

--
Craig
-----------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438
View this thread: http://www.excelforum.com/showthread.php?threadid=37970