Copy Visible Cells in Sheet with Merged and Hidden Cells
 

How do you copy only visible cells on a sheet that has merged and hidden
cells. Not as easy as everyone thinks. I keep getting the error message
stating " Cannot change part of a merged cell." Any suggestions? This isn't
a simple ctrl c......ctrl v. I've tried special select of visible cells and
copy and got the error stated above.

Answer: Copy Visible Cells in Sheet with Merged and Hidden Cells
 

Copying only visible cells on a sheet that has merged and hidden cells can be a bit tricky, but there are a few ways to do it. Here's one method that should work:

1. Select the range of cells that you want to copy, including any merged or hidden cells.
2. Press F5 to open the "Go To" dialog box.
3. Click the "Special" button at the bottom of the dialog box.
4. In the "Go To Special" dialog box, select "Visible cells only" and click "OK".
5. Press Ctrl+C to copy the selected cells.
6. Click on the cell where you want to paste the copied cells.
7. Press Ctrl+Alt+V to open the "Paste Special" dialog box.
8. In the "Paste Special" dialog box, select "Values" and click "OK".

This should paste only the visible cells, without any merged or hidden cells causing errors. If you need to preserve any formatting or formulas, you can select the appropriate options in the "Paste Special" dialog box.

Copy Visible Cells in Sheet with Merged and Hidden Cells
 

hi
no, you complcated it with merged cells so it is not longer a simple ctrl
c....ctrl v.
get rid of the merged cells and your problem with mysteriously go away.

regards
FSt1

"rtwiss" wrote:

How do you copy only visible cells on a sheet that has merged and hidden
cells. Not as easy as everyone thinks. I keep getting the error message
stating " Cannot change part of a merged cell." Any suggestions? This isn't
a simple ctrl c......ctrl v. I've tried special select of visible cells and
copy and got the error stated above.

Copy Visible Cells in Sheet with Merged and Hidden Cells
 

Merged cells stem from a database output which i have no control. So thanks
but no thanks!

FSt1 wrote:
hi
no, you complcated it with merged cells so it is not longer a simple ctrl
c....ctrl v.
get rid of the merged cells and your problem with mysteriously go away.

regards
FSt1

How do you copy only visible cells on a sheet that has merged and hidden
cells. Not as easy as everyone thinks. I keep getting the error message
stating " Cannot change part of a merged cell." Any suggestions? This isn't
a simple ctrl c......ctrl v. I've tried special select of visible cells and
copy and got the error stated above.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

Copy Visible Cells in Sheet with Merged and Hidden Cells
 

If you cannot or will not unmerge the cells, you are doomed.

What is preventing you from selecting the range and FormatCellsAlinment
and unmerging them?

No data will be lost


Gord Dibben MS Excel MVP

On Wed, 01 Oct 2008 23:23:36 GMT, "rtwiss via OfficeKB.com" <u46610@uwe
wrote:

Merged cells stem from a database output which i have no control. So thanks
but no thanks!

FSt1 wrote:
hi
no, you complcated it with merged cells so it is not longer a simple ctrl
c....ctrl v.
get rid of the merged cells and your problem with mysteriously go away.

regards
FSt1

How do you copy only visible cells on a sheet that has merged and hidden
cells. Not as easy as everyone thinks. I keep getting the error message
stating " Cannot change part of a merged cell." Any suggestions? This isn't
a simple ctrl c......ctrl v. I've tried special select of visible cells and
copy and got the error stated above.

Copy Visible Cells in Sheet with Merged and Hidden Cells
 

I will have to unmerge while splitting data then copy visible cells. Not
sure how to do all that. But this is a start. What do you think?
Sub Unmerge()
Dim c
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
MsgBox c.Address & " is merged"
End If
Next
Dim rng As Range, rngtot As Range, rngval As Variant
Dim strtrow As Long, endrow As Long, col As Long

strtrow = Selection.Row
col = Selection.Column
endrow = Application.WorksheetFunction.Min(Selection.End(xl Down).Row - 1,
Cells(65536, col).End(xlUp).Row + 1)
rngval = Selection.Value

Set rngtot = Range(Cells(strtrow, col), Cells(endrow, col))

ActiveCell.Unmerge
For Each rng In rngtot
rng.Value = rngval
Next rng

End Sub

Gord Dibben wrote:
If you cannot or will not unmerge the cells, you are doomed.

What is preventing you from selecting the range and FormatCellsAlinment
and unmerging them?

No data will be lost

Gord Dibben MS Excel MVP

Merged cells stem from a database output which i have no control. So thanks
but no thanks!
[quoted text clipped - 12 lines]
a simple ctrl c......ctrl v. I've tried special select of visible cells and
copy and got the error stated above.

--
Message posted via http://www.officekb.com