Copy cell to first BLANK cell in column, NOT the first blank cell from the last row used

[Eyeson15]

Hi,

Please help me create a VBA code.

I have been googling for a long time and the solutions it provides is not quiet what I need.

I would like to copy a cell and paste into FIRST BLANK CELL in column B.

Column B is a long column of data and it has gaps. So the cell I select needs to be copied into the first BLANK gap in column B.

All the solutions seem to paste it at the bottom of column B after the last non-blank cell.

Thank you!

James

 

[Eyeson15]

Hi Rick

I have no problem sharing but thought I was helping the helping community by not presenting a huge script that induces headache attacks

Your code, My Aswer's code works well for blank cells. Thank you so much for your efforts.

You are absolutely right, the blank cells are probably displaying "".

Hope all this info also helps someone else. The code I put in post 2 is serving me nicely (albeit bit clunky).

Thanks again.

James

 

[Rick Rothstein]

You are absolutely right, the blank cells are probably displaying "".

Curious... you have formulas in Column B that you plan to overwrite with constants? Anyway, given that, this will tell you the row number of the first blank cell (no matter if it is truly blank or has a formula displaying "") in Column B which you can use to form a cell reference with...

[B][COLOR="#008000"]' If you declare your variables, put this line with them[/COLOR][/B]
Dim LastRow As Long, FirstBlankRow As Long

LastRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row + 1
FirstBlankRow = Evaluate(Replace("MIN(IF(B1:B#="""",ROW(B1:B#)))", "#", LastRow))

 

[Eyeson15]

Wow. How are you so good with Excel? Wish I was like you.

James