Solved: Transposing data where some data is in merged cells

[nathan2314]

Hi
I have wrote code that transposes data from other workbooks into a master workbook that compiles all the data. That is working fine. But a problem I'm having is that some of the data, which is initially in columns, is in merged cells. So when I copy the whole data range and paste it transposed in the master file, the data that was in merged cells causes blank columns in the transposed data. For example, if cells A1 and A2 are merged together and data is entered, that data is given the location of A1. So when I transpose and paste in the master file, those two cells get put into A1 and B1. But the data itself is just put in A1 and B1 is blank. So any more transposed data starts in C1. How can I transpose the data so that no blanks appear. Is there a way to go through after I get done transposing all the data and collapse all the data to get rid of all blanks??

Appreciate any help!!

:)

[austenr]

First of all, merged cells cause problems thats why I avoid them. The best suggestion without seeing your code is the very first thing in your code to select all of the data you are going to copy and un merge the cells.

This might also clear up the blanks you are getting.

If you are still getting blank cells you can probable find code on this site or on the web to eliminate the empty cells.

If you are still having problems, post a sample workbook and let us take a look.

ex from the help files:

With Range("a3")
If .MergeCells Then
.MergeArea.UnMerge
Else MsgBox "not merged"
End If
End With

[nathan2314]

Hi
Thanks for your reply. I'm trying to implement your approach but with no luck so far. I don't have a say in the format of the Excel files I'm copying from. They are for other people to input survey data. So the merged cells are part of the format. So I wrote the code below but its not working. Part of the problem is that the range of cells I'm copying to paste in the master file has some cells merged and others not. So in the code below I'm attempting to cycle through the selection and unmerge everything. But not luck. How can I change it so it will unmerge everything, then I can transpose and paste.
Thanks for you help!!!

mybook.Sheets("Protect Population").Range("D6:D29").Copy
With Selection
For Each rcell In Selection
With rcell
If .MergeCells Then
.MergeArea.UnMerge
End If
End With
Next rcell
End With
basebook.Sheets("Protect Population").Range("A" & endrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

[austenr]

Looks like you are copying the selection without doing the un merge first. try this:


With Sheets("Protect Population")
.Range("D6:D29").Select
With Selection
For Each rcell In Selection
With rcell
If .MergeCells Then
.MergeArea.UnMerge
End If
End With
Next rcell
End With
End With
Sheets("Protect Population").Range("D6:D29").Copy

basebook.Sheets("Protect Population").Range("A" & endrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

[nathan2314]

Thanks! That worked. So it is now unmerging the all the cells in the range. But I still can't copy and just paste because unmerging the cells left several blank cells in the range. Is there a quick way I can copy only the nonblank values in the range and transpose paste that?

[austenr]

Sorry I cant help you more right now. Work is calling. Im sure someone will come along and tidy up for you.

[austenr]

so if you are transposing rows to columns why dont you just create another macro to delete the blank rows after you finish?


sub blah()
' select sheet and rows as above here.
For i = Selection.Rows.Count To 1 Step -1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

Selection.Rows(i).EntireRow.Delete

End If

Next i
end sub


now just select your range of transposed data, and insert "blah" after the last line of the code i gave you above. That will call the sub above and remove the blanks.

Remember you need to have the subs in the same module.

[nathan2314]

That worked great!! Thanks!

[austenr]

no problem. glad to help