Copy formula-resulting values from a range to the first "empty" cell in another column

[messingaroundok]

Hello all,

So I have a column of values ("E") that are the result of formulas. Is it possible to copy all resulting values of Column "E" into the first unoccupied cell in Column "D" (that also has values that depend on the result of a formula)?

I have tried a combination of Range ("E1:E) with .SpecialCells(xlCellTypeVisible).Copy to no avail.

Any help would be greatly appreciated. Thanks in advance!

 

[messingaroundok]

OK. Try this:

Sub MyCopy()

    Dim lastRowD As Long
    Dim lastRowE As Long
    
'   Find last row in columns D and E
    lastRowD = Cells(Rows.Count, "D").End(xlUp).Row
    lastRowE = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Copy and paste value from column F1:F17 to first available row in D
    Range("F1:F17").Copy
    Range("D" & lastRowD + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

'   Copy and paste value from column E to first available row in D
    Range("E1:E" & lastRowE).Copy
    Range("D" & lastRowD + 18).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

'   Find new last row in column D
    lastRowD = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Copy and paste value from column F30:F55 to first available row in D
    Range("F30:F35").Copy
    Range("D" & lastRowD + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
End Sub

Because you know that you are copying exactly 17 rows in column 1, you don't need to recalculate the last row in column D dynamically. Just add 17 to the original last row calculation. However, after the second one, since you just copied an unknown number of rows, you will need to do another last row calculation for column D.

It actually wouldn't hurt to do the calculation after each step, it would still come up with the same thing.

Hi Joe,

Thanks again for your response and help. The code you supplied resulted in the last bit (Range F30:F55) moved to D919 .... I have no understanding why....

 

[Joe4]

What is the LastRowE variable returning?
You can add a simple message box after its calculation to see.

'   Find last row in columns D and E
    lastRowD = Cells(Rows.Count, "D").End(xlUp).Row
    lastRowE = Cells(Rows.Count, "E").End(xlUp).Row
MsgBox lastRowE

Do you have any formulas in column E past your last row of data?

 

[messingaroundok]

Hi again,

It resulted with 800 -- and it dawned on me that it was including the cells with formulas that didn't result in values. Once I eliminated those cells, your code worked perfectly. Thanks again, Joe!

 

[Joe4]

You are welcome!

Yes, I thought it was probably something like that.