Copy Visible Cells in Sheet with Merged and Hidden Cells

R

rtwiss

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.
 
F

FSt1

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
 
R

rtwiss via OfficeKB.com

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

Gord Dibben

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

What is preventing you from selecting the range and Format>Cells>Alinment
and unmerging them?

No data will be lost


Gord Dibben MS Excel MVP
 
R

rtwiss via OfficeKB.com

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(xlDown).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 said:
If you cannot or will not unmerge the cells, you are doomed.

What is preventing you from selecting the range and Format>Cells>Alinment
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top