Macro transfer data from a column with a twist

[Elfiraon]

Hey all,

I'm trying to create a macro that will do the following:

When macro is activated; In a column (eg. column A) go to the first cell that has a value other than "0" and then copy, then keep going down cell by cell until it finds a blank cell, once the values are selected, copy and paste them in a different column (eg. column B or column A of different sheet).

Any input/help is appreciated, thanks!

 

[alansidman]

Try this:

Option Explicit


Sub copyZero()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim newR As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    For i = 1 To lr
    newR = Range("B" & Rows.Count).End(xlUp).Row
    If Range("A" & i) <> 0 Then
    Range("A" & i).Copy Range("B" & newR + 1)
    End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"


End Sub

 

[Elfiraon]

What if I wanted to paste the data into the next empty cell of column B?

 

[alansidman]

That is exactly what the code does.


crosspost: http://www.ozgrid.com/forum/showthread.php?t=191219