VBA: Copy Range of Cells until Null

[hamistasty]

I want to copy a list of cells from sheet "schedule", column A row 9 down until there is a null value, and then paste them into a new sheet "index".

Sheets("Index").Range("C13") = Sheets("Schedule").Range("A" & Rows.Count).End(xlUp).Row

the above code doesn't work and now I'm stuck.

tia

 

[cstimart]

Try this...

Sheets("Schedule").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Index").Range("C13")

 

[AlphaFrog]

With Sheets("Schedule")
    .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Index").Range("C13")
End With

 

[hamistasty]

Thanks, that works great. However, when I use:

With Sheets("Schedule")
       .Range("O9", .Range("O" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Index (2)").Range("G13")
End With

Sometimes there will be no value in cell O9, or there will be a break between null values (ie. O9 will have a value, O10 will not, then O11 will.)

So what I want it to do now, is refer to A9 down (until it ends) and just copy O9 to Ox with x being the count of A9 down.

any help?

 

[hamistasty]

What am I doing wrong here wrt the above:

x = Application.WorksheetFunction.CountA(Sheets("Schedule").Range("A9:A" & Rows.Count))
 
With Sheets("Schedule")
    .Range("A9", .Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Index (2)").Range("C13")
    .Range("O9", .Range("O" & x)).Copy Destination:=Sheets("Index (2)").Range("G13")
End With

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> Sheets("Schedule")<br>    .Range("A9", .Range("A9").End(xlDown)).Offset(, 14).Copy Destination:=Sheets("Index (2)").Range("G13")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

 

[hamistasty]

Thanks! perfect.

How do I make it so this works where G13 is a merged cell with H13 but O9 down is one column (ie single cells being copied over to a merged cell)

x = Application.WorksheetFunction.CountA(Sheets("Schedule").Range("A9:A" & Rows.Count))
    Dim myRow As Long, newRowCount As Long
    myRow = 13
    newRowCount = x - 1
    With Sheets("Index (2)").Rows(myRow)
        .Copy
        .Offset(1).Resize(newRowCount).Insert
    End With
With Sheets("Schedule")
    .Range("A9", .Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Index (2)").Range("C13")
    .Range("A9", .Range("A9").End(xlDown)).Offset(, 14).Copy Destination:=Sheets("Index (2)").Range("G13")
End With

 

[AlphaFrog]

Merged cells are considered evil and vile. Use center across selection instead. This is a Horizontal cell formatting option.

Unmerge the two cells and use the code below.

<font face=Courier New>Sheets("Index (2)").Range("G13:H13").ClearContents<br><SPAN style="color:#00007F">With</SPAN> Sheets("Schedule")<br>    .Range("A9", .Range("A9").End(xlDown)).Offset(, 14).Copy Destination:=Sheets("Index (2)").Range("G13")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>Sheets("Index (2)").Range("G13:H13").HorizontalAlignment = xlCenterAcrossSelection</FONT>