VBA merge cell

[Chaozfate]

Dear All,

I m having this vba running, basically copy D3:H6 into the last row without data.

Sub CR()
Sheets("CR").Range("D3:H6").Copy
lastrowCR = Sheets("CR").Range("D35").End(xlUp).Row
Sheets("CR").Activate
Sheets("CR").Cells(lastrowCR + 4, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

My question is within the range (D3:H6), (F3:F6) is a merged cells, and i wish the excel could paste the exact info into the last row without data, as well as merge the F+4 cells.

Can anyone help with my request?

 

[VBE313]

Not sure what you mean by pasting "without data". Do you mean without formulas?

Here is a code that will do what I think you are asking for....

Sub Chaozfate()
Dim lastRow As Long, strCells As String
With Sheets("CR")
    lastRow = .Cells(.Rows.Count, 4).End(xlUp).Row + 4
    .Range("D3:H6").Copy
    .Cells(lastRow, 4).PasteSpecial
    strCells = .Cells(lastRow, 4).Address & ":" & .Cells(lastRow + 3, 7).Address
    .Range(strCells).Value = .Range(strCells).Value
End With
End Sub

 

[Chaozfate]

Sorry, did not make it clear, was writing in my own language.... by last row without data = i am referring to next empty row... bu

I tried your code, it successfully merge the cells as intended, however, i forgot to inform that D3:H6 are actually formulated. My apologies.
So when i tried to copy paste, the pasted data are formulated and showing wrong result. I tried inserted the paste as value code, end up the merged cells cannot be pasted as merged.