Paste only into blank cells...

HookEm

Board Regular
Joined
Jun 26, 2002
Messages
85
I have a worksheet with 929 rows of 26 columns. Appx 75% of the fields are populated but the other 25% are blank. I need to send this spreadsheet out to other offices to get them to fill in the blanks that they are responsible for. I have created drop down lists for each column that has blanks in it to help them enter their data easier. I want to copy the created drop down list cell and paste it into the blanks for each column. Is there a way to do this without scrolling down through each of the 929 rows and sticky selecting only the blank cells and then pasting? In other words, is there a way to copy and then paste into only the empty cells of a range?

Hook Em
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Not sure if this wil work, but if you copy from the file that you sent out, and then do a PasteSpecial|Skip Blanks back into your master file, this should work. Test it and see if it gives the desired results.

If not, then we might need to look at a VBA solution.
 
Upvote 0
TommyGun - thanks for the reply. I believe that will work well for consolidating my data after I get it back but I am trying to prepare the sheet to send out to them to fill out and send back.

I thought the drop down boxes would help them enter their data faster and easier. I hoped their might be a quick way to paste the drop down boxes into the empty cells without going through all 929 rows one column at a time.

Hook Em
 
Upvote 0
If I follow you correctly, why don't you:

(1)
Copy the cell with the drop-down (presumably it's a data validated cell)

(2)
Select the range of interest that contains blanks.

(3)
Click Edit > GoTo > Special > select Blanks, click OK.

(4)
Click Edit > Paste.

You can do it with a macro I guess, if you have 26 different drop-downs for 26 columns, but if it's just 1 drop down for the entire range, the above steps would do that in a snap. Any help or did I miss the boat on the question?
 
Upvote 0
Perfect help - you are the Captain of the boat! This worked perfectly - I never knew you could do that. Thank you very much for your response.

Hook Em
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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