Copy Offset cell range and move right

[Rowdee26]

I have a worksheet, "data". Column 'B' searches for cells to last row with data and pastes to the right over writing data, which is ok. Currently this works ok for cell 'B', but I am trying to redo the code to copy cell 'C' also to move cells 'B' and 'C' to the right but this is not working. See following code I am trying.
Appreciate any guidance you can give as I have been trying to complete coding for the last year and this is my first post. May need help on protocols also during posting.

lr = ActiveSheet.UsedRange.Rows.Count
For a = 2 To lr
If Cells(a, 2).Value <> "" Then
Cells(a, 7).Value = Cells(a, 2).Resize(, 2).Value
End If
Next a

 

[myall_blues]

With your original code you can use

Sub test()
    Dim lr As Long
    Dim a As Long
    lr = ActiveSheet.UsedRange.Rows.Count
    For a = 2 To lr
        If Cells(a, 2).Value <> "" Then
            Cells(a, 7).Resize(a, 2).Value = Range(Cells(a, 2), Cells(a, 3)).Value
        End If
    Next a
End Sub

Alternatively (and much faster) you can use an array to copy/paste the data

Sub test2()
    Dim lr As Long
    Dim arr
    lr = ActiveSheet.UsedRange.Rows.Count + 1
    arr = Range(Cells(2, 2), Cells(lr, 3))
    Cells(2, 7).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

As a 'protocol' suggestion, please use the code tags when posting code or formulas. You can find the buttons to insert them at the top of the edit window when posting.

 

[dermie_72]

If im understanding you correctly you want to copy the values in B AND C into columns G and H but only if there is a value in B.
if that is the case, you can adjust the middle code to

Cells(A, 7).Resize(, 2).Value = Cells(A, 2).Resize(, 2).Value

and it should work. If you only want to do this when there is a value in B & C then you will need to consider that in your if statement.

 

[Rowdee26]

@myall_blues:
test was copying correctly but was pasting xtra cells down. Just tweaked the code - Cells(a, 7).Resize(, 2).Value - to stop doing that and now works great.
test2 array was copying all cells from column B - blank & non blank - with column C to last row. If you have a solution I definitely would like to see that as arrays is absolutely foreign to me.
@dermie_72:
Your code was similar but copying different cell and pasting to the desired location to last row.

Thanks to myall_blues and @dermie_72 for your replies.
I will take it in and continue learning!

 

[myall_blues]

Apologies about the skipping blanks bit.
Code below for array method, with comments for reference.

Sub test2()
    Dim lr As Long, i As Long
    '
    ' Arrays need to be declared as variant
    '
    Dim arr
    '
    lr = ActiveSheet.UsedRange.Rows.Count + 1 ' +1 because data starts in row 2
    '
    ' Read the entire range of interest into the array
    '
    arr = Range(Cells(2, 2), Cells(lr, 3))
    '
    ' LBound(arr,1) is the first row of the array, UBound is the last
    ' Likewise, LBound(arr,2) is the first column of the array
    '
    ' Loop through the array
    '
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 1) <> "" Then
            '
            ' Write the first column array value
            '
            Cells(i + 1, 7).Value = arr(i, 1)
            '
            ' Write the second column array value
            '
            Cells(i + 1, 8).Value = arr(i, 2)
        End If
    Next
End Sub

 

[Rowdee26]

Thanks for the reply Murray.
As this is the 2nd part of my reporting macro to go with the first part i have completed, I have decided to use this array for my macro as it is significantly faster to action with the number of data lines I have.
I look forward to educating myself on these arrays.

I appreciate your time and effort with your above coding.
If I don't hear from you over the next couple of months, all the best for the festive season.

Rod.

 

[myall_blues]

Thanks Rod, and the same to you.

If you can use an array, even if you have to loop through it as in this case, it will always be substantially faster than reading and writing to cells.