Paste to Visible Cells only NOT WORKING

[yoyomasson]

My goal is to get all numbers that are negative in my A column to be positive.

I filter column A to show only records with negative numbers (there are now hidden cells) (simple example, A2=-1 , A3=2, A4=-3, filter so I can now only see A2 and A4). I insert a column (B) and do "=abs(A1)" and drag that down. This works to get the absolute value of column A into column B, for only the visible records. (so I still only see rows 2 and 4; B2=1, B4=3)

Now the hard part. I want to copy these numbers to column A, but only paste to visible cells. I highlight my data range in column B and hit Alt + ; to only select visible cells. Now I hit Ctrl + c to copy. If I then click on the first cell in column A that I want to paste to and hit Ctrl + V, the data is pasted in all cells, no spaces, filling in the hidden cells, which I do not want (in my example the 1 from B2 goes to A2 and the 3 from B4 goes to A3).

If I select my entire 'paste to' data range in column A (A2:A4), hit ALT + ; to select visible only, and hit CTRL + V to paste, it tells me "This command cannot be used on multiple selections".

Any ideas? Other postings of seen seem to intimate that my first attempt described here should work (select visible only, copy, then paste), but doesn't! i can copy visible only easily enough, but when I paste, it ALWAYS pastes into all rows, hidden or not.

 

[AlphaFrog]

You wouldn't need to filter for negatives in column A because the ABS of a positive is unchanged.

Just copy all the ABS values in column B unfiltered to column A unfiltered.

 

[yoyomasson]

Thanks but that won't work. I'm working with Header and Trailer records on one sheet. So column A for row 1 (a Header record) doesn't mean the same thing for row 2 (a trailer record). Actually I need column A to be blank for the Trailer records. This method would work, I would just get 0s in the Trailer records, then I would have to filter to see just Trailer records and blank column A out again. Either way, I'd still like to know how to paste to visible only. Any ideas?

 

[AlphaFrog]

You can copy multi-area visible cells and paste them to one contiguous area.
You can copy one cell and paste it to multi-area visible cells

You cannot copy multi-area visible cells and paste them to other multi-area visible cells even if all areas in the copy\paste coincide. You could do this in VBA, but you would actually loop through each of the areas and do multiple copy\pastes.

There may be some "tricks" you could use to achieve your copy\paste, but I didn't really follow your data configuration.

Try this if it suits your data:

• Filter column A for negatives
• Put your ABS formula in column B =ABS(A2) for each of the visible cells
• Unfilter column A
• Copy column B
• Select column A and Paste Special: Values and Skip Blanks

The Skip Blanks will not paste over the values in column A where there are blank cells in column B.


Another method:

• Filter column A for negatives
• Put a -1 in any empty cel and copy it
• Select column A and Paste Special: Values and Operation-Multiply

​The Operation Multiply will multiply each of the visible negative values by -1 and make them positive.

 

[yoyomasson]

Genius, pure genius! Both methods work well. The second one is easier.

Thanks so much for your help.

 

[jamilm]

Dear Alpha,

referring to your comments, you mentioned that you can do this in VBA? any idea how to do this through VBA?

thanks.

You can copy multi-area visible cells and paste them to one contiguous area.
You can copy one cell and paste it to multi-area visible cells

You cannot copy multi-area visible cells and paste them to other multi-area visible cells even if all areas in the copy\paste coincide. You could do this in VBA, but you would actually loop through each of the areas and do multiple copy\pastes.

There may be some "tricks" you could use to achieve your copy\paste, but I didn't really follow your data configuration.

Try this if it suits your data:

• Filter column A for negatives
• Put your ABS formula in column B =ABS(A2) for each of the visible cells
• Unfilter column A
• Copy column B
• Select column A and Paste Special: Values and Skip Blanks

The Skip Blanks will not paste over the values in column A where there are blank cells in column B.


Another method:

• Filter column A for negatives
• Put a -1 in any empty cel and copy it
• Select column A and Paste Special: Values and Operation-Multiply

​The Operation Multiply will multiply each of the visible negative values by -1 and make them positive.

 

[AlphaFrog]

Dear Alpha,

referring to your comments, you mentioned that you can do this in VBA? any idea how to do this through VBA?

thanks.

Use the .Areas property of the visible cells...

    [color=darkblue]Dim[/color] rngArea [color=darkblue]As[/color] Range
    [color=darkblue]For[/color] [color=darkblue]Each[/color] rngArea [color=darkblue]In[/color] Range("A:A").SpecialCells(xlCellTypeVisible).Areas
        [color=green]'do something with each contigious area here[/color]
    [color=darkblue]Next[/color] rngArea

 

[jamilm]

thanks Alpha.

Use the .Areas property of the visible cells...

    [COLOR=darkblue]Dim[/COLOR] rngArea [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rngArea [COLOR=darkblue]In[/COLOR] Range("A:A").SpecialCells(xlCellTypeVisible).Areas
        [COLOR=green]'do something with each contigious area here[/COLOR]
    [COLOR=darkblue]Next[/COLOR] rngArea