Paste into specified cell if empty, if not go to next empty in row

**Dan23cd** · 02-17-2013, 08:24 PM

OK -- working on a macro that first copies a selected range from sheet one to sheet two. Then I want to average the rows and move the averaged numbers to (as values) to a specified location. I will then go back to sheet one and repeat this -- however when the number is moved to the specified location I want the macro to only paste in that location if the cell is blank -- if a value is already there, then go to the next cell in the row -- and repeat the procedure until a blank cell is found for pasting.

I have no problems getting macro to copy and past appropriately into sheet 2 then average -- but do not know how to do the vba code for finding the blank/empty cell.

e.g. Copy A6, past special as vale into A16 if empty, if not empty, go to A17, etc.

What is the VBA code to do this -- am using excel 2007 and 2010. Thanks for your help.

**Leith Ross** · 02-17-2013, 08:51 PM

Hello Dan23cd,

Welcome to the Forum!

When asking questions about macros, you should provide either the code you have questions about or the workbook. Generally, the workbook is the best of the two options.

To Post Your Workbook

At the bottom right of the Reply window, Click the button Go Advanced
At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
Click the Select Files button at the bottom middle of the dialog.
In the new window Find the file you want to upload, select it, and Click Open.
You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Pic

**Dan23cd** · 02-19-2013, 02:27 PM

I have attached a workbook in which I would like to do the following -- number ranges are selected on sheet 1 and transferred to sheet 2 -- I have highlighted the numbers that were selected -- this must always be done by the user. It is done by a series of macros using the keyboard keys a,s,d,f and g. These macros then average the correct values and put them in a temporary place on sheet 2. Macro6 -- keyboard shortcut h is where I need help. I want the averages moved to the locations shown in sheet 2 -- but if that row is filled, then paste into next row. Otherwise I have to use a different macro each time, making it much more cumbersome.

So can someone look at the macro defined by the "h" on the keyboard and give me a way to paste the numbers into the next blank row.

Once I understand how this is done -- I may simply put in the earlier macros as would be ultimately simpler -- I have to move the values now so that when I go back the the sheet 1 and select the next group of 5 ranges, it will not overwrite what is already there.

If it helps to run some of the macros to see what is happening --select the range defined by W57..Y61, hit control a -- then down to the next highlighted, hit control s... etc until 5 selections are made. --ultimately, this will then be repeated with another set of 5 numbers, until the worksheet analysis is completed.