Sorting with merged cells

Unfortunately, merged cells cause many problems, especially with sorting. For this and other reasons, it is recommended that you avoid using Merged Cells.

There are other alternatives, such as centering across cells, which should give you the sane effect without the problems.

Check out this link for instructions on how to do that.
http://www.mrexcel.com/board2/viewtopic.php?t=76249

I tried that. Didn't work. If I take out the merged cells, how would I sort this column and keep all the following columns that i want with it? Like if column a is to be sorted alphabetically and but rows 1-5 go with the word is A1 and then the word in d6 is moved to a1 when sorted, how do i get the information in the 5 rows to move with D6 when it is moved to A1 and have the info in the 5 rows referrring to A1 move with it when it is sorted ?

Can you post 2 screen shots please, just so we can see the pattern of what you have now, and then how you want it to look after the sort is executed. The example in your first post is too inconclusive. You were talking about "5 or 6" rows of data but you only show 3 or 4 rows of data in your example. Also, point out any patterns such as if the record sets are always separated by an empty row, and if empty really means empty, not just blank-looking cells with spacebars or null string formulas, etc.

Also, you are posting this question on more than one thread:
http://www.mrexcel.com/board2/viewtopic.php?t=76249

Please stick to one thread or the other.

Can I just email you an example? I am not sure how to post it.

No emails with attachments please. Here's a link regarding how to post the screen shot, which will give everyone a chance to look at the question and suggest a possible solution, to your benefit.
http://www.mrexcel.com/board2/viewtopic.php?t=92622

Here is an example of what I am looking to be able to do. As shown, the user can add and delete the number of rows that go with each main topic. Therefore, the code has to somehow be able to track that. Is this even possible?

This is one way to do what you say you want.

Based on your screen shot, your data starts in row 1 and each section is separated by one empty row. Activate that sheet and run this macro from there...try it first on a copy of your workbook to make sure it does what you want.

This worked fine for me, tested no problem on xl2K3 xp.



Sub Test1()
With Application
.ScreenUpdating = False
.DisplayAlerts = False

Dim asn$, x&, Rowz&, Area As Range
asn = ActiveSheet.Name: Rowz = 0

Workbooks.Add 1
ThisWorkbook.Sheets(asn).Cells.Copy Cells
ThisWorkbook.Sheets(asn).Cells.Clear

Cells.UnMerge
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(4).Formula = "=R[-1]C"
Columns(1).Value = Columns(1).Value
Columns(2).SpecialCells(4).EntireRow.Delete
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

For x = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(x, 1).Value <> Cells(x - 1, 1).Value Then Rows(x).Insert
Next x
Columns(1).SpecialCells(4).EntireRow.Clear

For Each Area In Columns(1).SpecialCells(2).areas
With Range(Area.Address)
If .Rows.Count > 1 Then
.Offset(1).Resize(.Rows.Count - 1).ClearContents
.VerticalAlignment = xlCenter
.Merge
End If
End With
Rowz = Rowz + Area.Rows.Count + 1
Next Area

Cells.Copy ThisWorkbook.Worksheets(asn).Cells
ActiveWorkbook.Close False

.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Still looking for help. I have update the spread I am workin on and the code doesnt quiet work. So, Below is an example of what I am looking to do.