Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells
When trying to sort a worksheet, a box is displayed saying "This
operation requires the merged cells to be identically sized". (The current worksheet contains data copied from a number of other worksheets) In the current worksheet, how do I find which cell(s) are the merged cells? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells
There may be better ways, especially in later versions in than my XL97 but
assuming that the last used row is Row 250 then you could use: Sub Merger() LastRow = 250 Application.ScreenUpdating = False For x = 1 To Columns.Count Columns(x).Select If Selection.Columns.Count 1 Then For y = 1 To LastRow Cells(y, x).Select If Selection.Columns.Count 1 Then Application.ScreenUpdating = True Cells(y, x).Select MsgBox "Merged cell " & Cells(y, x).Address GoTo GetOut Application.ScreenUpdating = False End If Next y End If Next x GetOut: Application.ScreenUpdating = True End Sub The code will stop after you dismiss the messagebox so that you can unmerge the cells then run the code again. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GARY" wrote in message oups.com... When trying to sort a worksheet, a box is displayed saying "This operation requires the merged cells to be identically sized". (The current worksheet contains data copied from a number of other worksheets) In the current worksheet, how do I find which cell(s) are the merged cells? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells
Sub findmerge()
Dim r As Range Dim rr As Range For Each r In ActiveSheet.UsedRange If r.MergeCells = True Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Select End If This will find all merged cells in the worksheet. -- Gary''s Student - gsnu200719 "GARY" wrote: When trying to sort a worksheet, a box is displayed saying "This operation requires the merged cells to be identically sized". (The current worksheet contains data copied from a number of other worksheets) In the current worksheet, how do I find which cell(s) are the merged cells? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells
Where have you been? I spent 2 hours trying to check for Merged Cells in
code because I did not that the keyword was MergeCells not MergedCells <g -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Gary''s Student" wrote in message ... Sub findmerge() Dim r As Range Dim rr As Range For Each r In ActiveSheet.UsedRange If r.MergeCells = True Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Select End If This will find all merged cells in the worksheet. -- Gary''s Student - gsnu200719 "GARY" wrote: When trying to sort a worksheet, a box is displayed saying "This operation requires the merged cells to be identically sized". (The current worksheet contains data copied from a number of other worksheets) In the current worksheet, how do I find which cell(s) are the merged cells? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells
Posted previously for Excel 2003
Try This: Use CTRL - F to open a Find And Replace Dialog Box Click the options button Make sure that the Find What Box is empty Click the Format Button Go to the Alignment Tab Remove ticks from Wrap Text and Shrink To Fit They will be greyed out but a second click will remove the tick Click the Merge Cells to remove the grey shadow and leave the tick in a clear box OK back to the Find And Replace Screen Click Find All Button All merged cells are shown in the lower pane Click on each one to go to them one at a time Hope this helps!! Enjoy -- A White --------------------------------------------------------------------------- All Outgoing Mail Scanned By Norton Antivirus 2003 --------------------------------------------------------------------------- "GARY" wrote in message oups.com... When trying to sort a worksheet, a box is displayed saying "This operation requires the merged cells to be identically sized". (The current worksheet contains data copied from a number of other worksheets) In the current worksheet, how do I find which cell(s) are the merged cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells are Merged. How to know where to where??? | New Users to Excel | |||
Merged Cells | Excel Discussion (Misc queries) | |||
how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
Merged cells | Excel Discussion (Misc queries) | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |