Copy values from populated cells only, and identify those cell locations.

Hi Everyone - I am new to this forum, and just starting to get my feet wet with VBA. A pleasure to join you here.

Here's my problem:

On Sheet 1, in Column A, the cells contain numbers, text and blanks, in no particlar order. I am trying to write a macro that will copy the contents of populated cells only, ignoring the blank cells. The contents of the populated cells need to be copied to another sheet (Sheet 2), and must appear in the same order (vertically), while ignoring/removing blank cells in the column. Essentially, I want to copy Column A from Sheet 1 to Sheet 2, but remove blank cells in the process.
At the same time, I need to identify the address of each cell that is populated on Sheet 1, and show each address in Column D on Sheet 3, in consecutive cells (again, no blanks).

Also note, the number of rows is not always the same...can be 30 rows, or 1000 rows, depending on the amount of data available.


This is above my current abilities , but I am confident that someone here can help.
Thanks All.

hi The Machinist, welcome to Excelforum, option, please check attachment, press Run button

Thanks watersev! That works perfectly! Simple and elegant code. Much appreciated.
Cheers!