Copy all text from a merged cell VBA

[LordSnow548]

Hello all,

I have a need to copy text from a set of merged cells using VBA by being able to click on a button, what i have thus far is:

Private Sub CommandButton2_Click()
'Copy Single cell
Range("E6:G28").Copy
End Sub

It works well, but its copying a lot of dead space and not just the text, so it copies the text and then several lines of just nothing at all, is there anything i can do to the above to only copy the visible text?

 

[Joe4]

The size of Range("E6:G28") is 23 rows and 3 columns!
The cells may be be merged, but what cell exactly is the text entry made in?
Just copy the contents of that cell.

BTW, if you are using VBA, I HIGHLY recommend you get rid of merged cells! They cause nothing but issues for things like VBA, sorting, etc.
Most serious programmers will NEVER use them (any many will not touch any question that involves them).

For merging cells across single rows, the "Center Across Selection" formatting option works much better.
It gives you the same visual effect as merged cells, but without all the issues.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[LordSnow548]

The size of Range("E6:G28") is 23 rows and 3 columns!
The cells may be be merged, but what cell exactly is the text entry made in?
Just copy the contents of that cell.

BTW, if you are using VBA, I HIGHLY recommend you get rid of merged cells! They cause nothing but issues for things like VBA, sorting, etc.
Most serious programmers will NEVER use them (any many will not touch any question that involves them).

For merging cells across single rows, the "Center Across Selection" formatting option works much better.
It gives you the same visual effect as merged cells, but without all the issues.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Hey Joe,

Thanks for answer, i agree merged cells are the devil! Hence why im trying to find better options, the cell the entry is made in is E6, the cells were merged for aesthetics i guess, (Im not the original creator) and while it looked good it just isnt working well.

Ive had a look at the Center across selection and that seems to work great but is there a way to have the text aligned left and not centered? ( no big deal if this isnt an option) or is there a way to copy text from an activeX text box?

Thanks in advance,

 

[Joe4]

Center across selection and that seems to work great but is there a way to have the text aligned left and not centered?

If looking to left align, then why merge the cells at all???
I don't see the point of that of all.