Copying and pasting using SpecialCells in VBA

[romado33]

Hello, I'm a VBA beginner trying to copy all cells with data in the F column and paste them to their corresponding cells in the E column (which are all empty). Basically I want to shift all cells with data in F to the left one column. (This is for an export that dumps Excel data and the data for certain columns ends up getting split up between two columns and I'm trying to get it all back together in one.) The relevant code is below.

I get "that command cannot be used on multiple selections" on the Paste line, and on the Copy line sometimes too (It was working a while ago and now it gives the error message too). I tried shifting the cells with offset but I get the same error message.

Can anyone give me some ideas on how to proceed?

Thanks!

Please Login or Register  to view this content.

[AB33]

Should be

rng2.SpecialCells(xlCellTypeBlanks).PasteSpecial xlValues

[romado33]

Thanks AB33, any ideas on why the Copy line would be generating the error too?

[AB33]

Since you are selecting an entire column and if there are no constant, the code will error. You might think you have constants, but excel might think otherwise. You can test the column which has the constant by:

CRTL+G
Goto menu appears, then select Special- click constant and see if any of the cells are highlighted by excel. By the way, you need to select the range first.

[romado33]

Thanks AB33, but I do have constants in F and I get the same message on the COPY line even if I select the range before. Any further ideas?

[AB33]

Sorry! I did not notice that you can not use it with multiple selection. You can do one column at a time.

[romado33]

Any ideas on alternative ways to shift/move non empty cells over to the column to their left? Thanks.

[romado33]

I think I was over-complicating things, I just wrote a simple for loop to copy values of non empty cells in F into the Cells in E, and it works fine.