Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste distinct columns macro | Excel Discussion (Misc queries) | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
Using Macro to Save Copy of File to New Location | Excel Discussion (Misc queries) | |||
macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
Macro to copy cell contents number of columns | Excel Programming |