Copy records until null

[MC09]

Hi,

I used this code to copy all the records from column A. But I need a code that will copy the records until the next row is null. Please help. Thanks in advance.


With Sheets("Sheet2")
.Range(.Range("A1"), .Range("A65536").End(xlUp)).Copy
End With
Sheets("Test").[A65536].End(xlUp)(2).PasteSpecial Paste:=xlValues

Example:
1 AA
2 BB
3 CC
4
5 DD
6 EE
7 FF

* copy 1-3

 

[schielrn]

Try:

With Sheets("Sheet2")
.Range(.Range("A1"), .Range("A1").End(xldown)).Copy
End With
Sheets("Test").[A65536].End(xlUp)(2).PasteSpecial Paste:=xlValues

Hope that helps.

 

[MC09]

Thanks for the reply. Btw, I used this code....

Do While ActiveCell <> ""
counter = counter + 1
ActiveCell.Offset(1, 0).Activate
wksTest.Cells(9 + counter, 2) = ActiveCell.Value
wksTest.Cells(9 + counter, 3) = ActiveCell.Offset(0, 2).Value
wksTest.Cells(9 + counter, 4) = ActiveCell.Offset(0, 4).Value
Loop

 

[erik.van.geit]

Hi, MCO9,
WELCOME to the Board!!!

Your code might be working, but consider that "activating" cells will slow down the process. Also the "loop" is not needed. So take a look at schielrns suggestion.

kind regards,
Erik

 

[MC09]

Hi Erick,

Yup, actually the code is a little slow. But I also need to find a certain word wherein I will start copying and end before the null. Then look again for that word and so on.

Do While ActiveCell.Value <> "Main number"
        ActiveCell.Offset(1, 0).Select
    Loop
 
    myRow1 = ActiveCell.Row
    ActiveCell.Offset(2, 0).Activate
 
    Do While ActiveCell <> ""
        counter = counter + 1
        wksTest.Cells(counter2 + 9 + counter, 2) = ActiveCell.Value
        wksTest.Cells(counter2 + 9 + counter, 3) = ActiveCell.Offset(0, 2).Value
        wksTest.Cells(counter2 + 9 + counter, 4) = ActiveCell.Offset(0, 4).Value
        ActiveCell.Offset(1, 0).Activate
    Loop

 

[erik.van.geit]

you got my tip, if you want to persist and still use "activate" your code will always be slow

to find something in a range, just use the tool designed for that
FIND, method
check out the VBA helpfiles