Is there a way to copy all empty cells in a data set on one sheet and paste the empty cells into a data set on a different sheet?
For every cell in the “Sales Sheet” that is blank, the corresponding cell in the “Inserts” tab also has to be blank. With this, I need to through and delete the cells that correspond in the “Inserts” tab.
For example, Sales Sheet cells H2 and I2 are blank for, so Inserts cells D2 and E2 should be blank so there is no insert statement for those cells.
HIGH-LEVEL
METHODS A & B
Two approaches:
Method A: see google sheet sample
Method B: VB
Sub clr_corresponding()
'Pre-requisite: entirety of first data set (Sales) must be selected
On Error GoTo errtrap
addr_2 = InputBox("Enter top left cell of 2nd dataset - e.g. C15", "User input")
MsgBox ("If the entirety of List1 not already selected, you'll need to re-run this macro!")
For Each cell In Selection
cell.Activate
r_1 = ActiveCell.Row
c_1 = ActiveCell.Column
Exit For
Next
Selection.SpecialCells(xlCellTypeBlanks).Select
i = 0
For Each cell In Selection
cell.Activate
r_2 = ActiveCell.Row
c_2 = ActiveCell.Column
Range(addr_2).Offset(r_2 - r_1, c_2 - c_1).Clear
Next
'Range("K2").Offset(i - 1).Value = ActiveCell.Address
'cell.Address
'arr.Add (cell.Address) 'https://stackoverflow.com/questions/18097756/fastest-way-to-add-an-item-to-an-array
errtrap:
MsgBox ("Invalid selection")
Exit Sub
End Sub
COMPARISON
Ads (+) / Dis (-):
A:
B:
There is a pretty simple way you can do this.
Go to the sheet you have the blanks on. Sales Sheet. Highlight (select) the entire area of concern. Don't be bashful... if you are going to want the same thing on the Inserts page, you must be comprehensive here. So if the area of concern on the Inserts page is A1:LZS2020, then select that region. If you know the coordinates, use F5-GoTo and enter them.
Use F5-GoTo, select Special, and in the dialog, choose Blanks. Note that THEY are now the only cells highlighted.
Bring up the Named Range functionality. Create a Named Range called something like BlanksSalesSheet. Note that its "Refers to" formula will have a (however-) long list of cell addresses matching your highlighted cells. Save the results. Click on the Named Range and press Enter to move to the "Refers to" portion and when it is highlighted, copy it to the clipboard.
Return cell-side. Find an empty cell, press F2 to edit it and paste the material you just copied. Press Enter. Press Shift-Some Arrow Key to highlight a second cell, one that is empty, preferably. You are going to Find and Replace so that only cell addresses are left, no sheet name material. So figure out what that string is, highlight it in the one cell and copy it to the clipboard. Press Ctrl-H for the Find and Replace function. Paste into the Find What box. Make sure the With What box is empty. Replace All.
Copy the string of cell addresses: F2 (Edit), Ctrl-Home, Ctrl-SHIFT-End, Ctrl-C, ESC should do nicely. Go to the Inserts page. Enter the Named Range functionality. Create a second Named Range, appropriately named (perhaps BlanksInserts). Paste your string with its = into the "Refers to" box.
Done. It SOUNDS like a nightmare, but it LITERALLY took me 34 seconds to do it. It's one of those five-ten minute explanation things that someone says, at the end of it, "Oh, you mean do this?" and takes 10 seconds to do a thing making you look a boring fool in front of everyone. Yes, do that though.
I'll get to how to use it in a second. To deal with the field of blanks changing all the time, you already have the Named Ranges you need, you just use F5-GoTo to highlight the next set of blanks to be sure of and change the Sales Sheet's Named Range's "Refers to" entry. Copy it, edit it like above, and paste it into the Inserts sheet's Named Range's "Refers to" entry. So just half the work then.
OK, how to use. To the left of the Formula Editing bar above the cells, there is a box with a down triangle to see more lines and select from them all. What goes there? Well, if you haven't clicked in it yet, you just see the cell address for the active cell. Or range size ("5R x 2C" if you have five rows by two columns selected). You might see a Named Range name if you happen to have a selection that matches one highlighted.
Make sure you're on the Inserts sheet. Click that down triangle/arrowhead. Notice the two Named Ranges you created are listed. Maybe more, maybe LOTS more. In any case, find, AND click on, the Named Range for the Inserts page, perhaps it was BlanksInserts. Notice that suddenly a bunch of cells are highlighted. They are EXACTLY the cells that on the Sales Sheet are blank... So press DELETE and make them blank here.
Done.
EVERY cell in the range from the Sales Sheet page that was blank is now blank on the Inserts page too. Simple as that.
Notice as well that NONE of the other cells on the Inserts page are affected. So if cell A1 had "cow" in it, it still does.
(If that isn't important ("somehow" and "amazingly"), there's a far simpler way to do this: just select and copy to the Inserts page all the Sales Sheet's contents. But surely you don't want that so...)
So A1 on the Sales Sheet might have "43" while on the Inserts page A1 has "cow" which is presumably desirable. ONLY the cells that are blank on the Sales Sheet page got deleted on the Inserts page. Everything else on the Inserts page, filled with something or themselves blank, got left alone, stays as it was. Which is what you asked for.
Again, nightmare to describe. Utterly simple and fast to do.
