VBA for Copy / Paste / Paste Special Blanks

[ahennema]

I am trying to write a VBA macro where I can put my cursor in a cell at the top of a range of cells, where the number of rows can vary each time, and select the list of cells and then copy them to the adjoining row. When that is done I want to return to my original list and copy them again and use Paste Special Values to replace the formulas with just the cell values.

I am able to code the first part which is to select each row until a blank is found. I can then do the copy.

Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

How can I select the adjoining column and do the Copy and then do a Paste Special Values over the original selection?


Excel 2007 on Windows machine

Thanks, Arich

 

[venkat1926]

assuming dat in column A

tryk this macro

Sub test()
Dim r As Range
Set r = Range(Selection, Selection.End(xlDown))
r.Copy Selection.Offset(0, 1)
r.Copy
Selection.PasteSpecial xlPasteValues


End Sub

no empty cell in between.
y;ou waill lose the formula in original data

 

[ahennema]

Thank You!

That worked for me. I have had computer issues for the last week or two and have not been able to get back online to read the answers. I am back now. Once again thank you, your solution works for me.

Arich