Copy/Paste to next blank row...if

Ragram46

New Member
Joined
Dec 4, 2018
Messages
19
I have a macro that copies/pastes to next row-below, but what I am trying to accomplish is that in the event some data was deleted above last row pasted...it would know to post in that blank row above, then continue as normal pasting below.

Sub Data()
'Modified 12/5/2018 3:21:41 PM EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row + 1
If Lastrow < 12 Then Lastrow = 12
Range("C6:G6").Copy
Cells(Lastrow, "C").PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Basically, it is just a fancy way of saying:
- check cell C12, if it is blank, make LastRow=12
- otherwise, check cell C13, if it is blank, make LastRow=13
- otherwise, start in C12 and hit CTRL+Down arrow, which takes you to the next blank cell in that column (and make that the LastRow).

Once we have that value, then we just use the Copy code that you had before.
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Amazing. I am familiar with recording macros moreso than typing them. Right now the only conceivable way i see to have a macro that deletes a specific row of information from the sheet would be to record each macro as it pertains to each row. Is that the best idea? Also, how can I allow a cell to have the value deleted but not the formula?
 
Upvote 0
Right now the only conceivable way i see to have a macro that deletes a specific row of information from the sheet would be to record each macro as it pertains to each row. Is that the best idea?
I am not sure I follow. Why would you want multiple different macros for deleting different rows?
Recording the macro of deleting one row show you how to delete a particular row. You could then edit the row number to make it delete any particular row.
But if you want to make it dynamic, you would want to edit that code to either delete the active row, or take in some input to tell it which row to delete.

So, the macro recorder is a great tool to get started, but you often have to edit the code afterwards to make it more dynamic (if you do not want it to do that one literal thing every time).
If you need help, let us know how to determine which row should be deleted (i.e. is it the active selected row, or do you want a prompt to enter in the row number, etc).

Also, how can I allow a cell to have the value deleted but not the formula?
Also note that Google can be one of your best friends here! There is lots of good code out there.
I did a Google search on "excel vba delete hard-coded values not formulas", and this is the first thing that popped up (and there are many other links):
https://www.thespreadsheetguru.com/.../30/remove-only-non-formula-values-from-cells
 
Last edited:
Upvote 0
Ok this will take some time to digest and I can google search too...

The deletion of rows might be muddying the waters a bit...i dont see a huge need for it, other than simplicity but if you have to identify the specific row to delete, you're probably quicker to highlight and delete...

The one that blows my mind is the concept of deleting without losing formulas...gotta dig more. Thanks,
R
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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