Copying and pasting to visible cells only

[Guest]

Hi, I have a large spreadsheet with a lot of hidden rows. I want to copy
data from the visible rows in one column and paste it into the visible rows
in another column. I know about the Go to/Special/visible cells only
command, but it seems to only work if you’re copying visible rows and then
pasting them somewhere where there are no hidden rows. Is there any way to
do this, either through an already existing Excel feature or with a VBA
macro? Thanks in advance for any information!

 

[Dave Peterson]

There's nothing built into excel that allows you to do this.

If the visible cells are on the same rows of the same worksheet, you may be able
to sort your data first--to group what would be the visible rows. Then
copy|paste.

 

[Guest]

Hi, Dave, thanks for your response. The problem with sorting the data is
that the data I want to sort involves subtotals and I don't want to get them
out of order. It's hard to explain without seeing the spreadsheet, but I
want to take the information in all the visible cells and move it over one
columtn to the left on the same sheet.

Anyway, I just wanted to make sure there wasn't a feature hidden somewhere
that I was missing. Thanks again.

 

[Dave Peterson]

Select the visible cells.
Edit|goto|special|visible cells only
You should be on the top cell of the selection
type an equal sign (=) and then hit the left arrow
(or type an equal sign and point to the cell in the same row)
hit ctrl-enter to fill the selection with the same formula

If all the cells in that second column are values, you can convert the entire
column to values. Then clean up the "sending" cells if you need to.

 

[Guest]

Hi, Dave, thanks so much for your response. I tried your suggestion and it
worked great. It's just what I need. Thank you so much!