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

messingaroundok

New Member
Joined
Apr 15, 2016
Messages
25
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Assuming that there is at least one entry in column D, see if this does what you want:
Code:
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 E to first available row in D
    Range("E1:E" & lastRowE).Copy
    Range("D" & lastRowD + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
        
End Sub
 
Upvote 0
Assuming that there is at least one entry in column D, see if this does what you want:
Code:
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 E to first available row in D
    Range("E1:E" & lastRowE).Copy
    Range("D" & lastRowD + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
        
End Sub

Thank you so much!! What's weird, I forgot that I needed to copy and paste the Range ("F1:F17) in the first empty cell in D prior to copying the contents of column E, but it's copying over the contents of D or putting it near D1000 instead. I would think it would be rather simple using your code -- apparently not for me.... any suggestions?
 
Upvote 0
One way:
Code:
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 F 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
        
End Sub
 
Upvote 0
One way:
Code:
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 F 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
        
End Sub

Thank you again, Joe, for your help. The code worked, but I guess I'm trying to understand the logic of what I'm doing. I attempted to copy Range (F30:F55") after copying the range from column E, but it instead copied over part what I just copied from E:

' Copy and paste another value from column F to first available row in D following the contents of E
Range("F30:F55").Copy
Range("D" & lastRowD + 26).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

I know I'm doing something incredibly silly, but I guess I don't understand logic of finding the cell following the pasting of the previous cells. Could you possibly explain it so that a simpleton with very little VBA knowledge could understand it? Thanks again for everything, Joe!
 
Upvote 0
Your order of copying matters. In one instance, you knew exactly how many rows you were copying (17), and in the other case you did not.
If you do the know one first, then you can just increment the number be added to lastRowD. However, if you are doing the unknown one first, then you do not know how many to add to it ahead of time.

Instead of doing that, just recalculate the last row in column D again, and use that.
 
Upvote 0
Your order of copying matters. In one instance, you knew exactly how many rows you were copying (17), and in the other case you did not.
If you do the know one first, then you can just increment the number be added to lastRowD. However, if you are doing the unknown one first, then you do not know how many to add to it ahead of time.

Instead of doing that, just recalculate the last row in column D again, and use that.

But how would I do that if I'm not sure how many rows of E are occupied with data?

Also forgot to add that blank spaces are in the contents of E that I'm copying over.....

Is it possible to use something to the effect of:

Range("D" & lastRowD + 18 & "E1:E" & lastRowE).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
Last edited:
Upvote 0
OK. You are confusing me here with all the changing conditions are orders of things.

Can you please try re-stating the exact task (and order), and indicate which rows are known and which are not?
 
Upvote 0
OK. You are confusing me here with all the changing conditions are orders of things.

Can you please try re-stating the exact task (and order), and indicate which rows are known and which are not?

Sorry about that, Joe :(

So, here is what I need done in the exact order:

1) Copy and paste the Range (F1:F17) in the first empty cell in D (this range contains empty cells as well)
2) Copy and paste everything in Column E (which also contains empty cells) into the first empty cell of column D AFTER pasting the range from step 1
3) Copy and paste the Range (F30:F55) into the first empty cell of column D AFTER the copying and pasting of E from step 2

I hope that kinda makes sense. I'm sorry for the confusion.
 
Last edited:
Upvote 0
OK. Try this:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top