Finding Merged Cells

Is there any (easy) way of finding a cell or cells that 
have been merged?
I am trying to sort a (very large) spreadsheet but can't 
because Excel "...requires the merged cells to be the same 
size..."

I can always select all and remove the merge - but I am 
wondering why there are merged cells - hence the reason 
for trying to find them!
0
9/10/2003 12:34:55 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
487 Views

Similar Articles

[PageSpeed] 47

Many Thanks - I like the macro solution - it certainly 
works on a test setup - I'll try it on the real 
spreadsheet tomorrow
>-----Original Message-----
>Manually, you could divide and conquer.  Select half the 
range, hit ctrl-1 (to
>show the Format|Cell dialog).  Look at that Alignment tab 
and look at the Merge
>cells box.  If it's not checked, look in the other half.
>
>If it's a black check mark, you found it.
>
>If it's a grey check mark, you're getting warmer--it's in 
the selected range.
>
>Here's one way via a macro that looks at all the cells in 
the usedrange:
>
>Option Explicit
>Sub testme02()
>
>    Dim myCell As Range
>    Dim resp As Long
>    
>    For Each myCell In ActiveSheet.UsedRange
>        If myCell.MergeCells = True Then
>            If myCell.Address = myCell.MergeArea.Cells(1, 
1).Address Then
>                resp = MsgBox(Prompt:="found at:  " _
>                                & myCell.Address(0, 0) 
& " Of " _
>                                & myCell.MergeArea.Address
(0, 0), _
>                              Title:="Continue Looking?", 
_
>                              Buttons:=vbYesNo)
>                If resp = vbNo Then
>                    Exit Sub
>                End If
>            End If
>        End If
>    Next myCell
>
>End Sub
>
>If you're new to macros, you may want to read David 
McRitchie's intro at:
>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>Don Edmondson wrote:
>> 
>> Is there any (easy) way of finding a cell or cells that
>> have been merged?
>> I am trying to sort a (very large) spreadsheet but can't
>> because Excel "...requires the merged cells to be the 
same
>> size..."
>> 
>> I can always select all and remove the merge - but I am
>> wondering why there are merged cells - hence the reason
>> for trying to find them!
>
>-- 
>
>Dave Peterson
>ec35720@msn.com
>.
>
0
9/10/2003 11:41:48 PM
Thanks alot Don! Really helped me out in a crunch.
0
anonymous (74722)
11/6/2003 5:56:06 PM
Reply:

Similar Artilces:

Formatting Cell problem
Hello guys, I have one frustrating problem. I copied a table from Amsouth Bank and pasted it into Excel. I then wanted to do some calculations. No matter how i formatted the cell whether Number or Currency the number always appeared on the left hand side and would not calculate. Number are supposed to be aligned right. Any suggestions. This is frustrating! Thanks! Hi - select an empty cell and copy this cell - select your imported numbers - goto 'Edit - Paste Special' and choose 'Add' -- Regards Frank Kabel Frankfurt, Germany "Jacob" <Jacob@discussions.mic...

Cell Greater Than Cell in Adjacent Column
A B C D 1 112.44 106.92 Y is A1>B4 2 104.90 96.03 N is A2>B4 3 109.71 103.46 N is A3>B4 4 115.74 111.50 Y is A4>B8 5 111.55 110.40 N is A5>B8 6 112.85 112.56 N is A6>B8 7 113.27 113.80 N is A7>B8 8 118.58 114.59 Y is A8>B9 9 118.25 115.85 Y is A9>B12 10 99.75 115.83 N is A10>B12 11 100.25 104.14 N is A11>B12 12 103.69 101.00 Y is A12>B13 13 103.58 99.84 Y is A13>B14 14 107.99 100.75 Y is A14>B15 15 106.33 104.27 I have two columns to which data is entered at the top by inserting a blank row and then adding the data points. The formula that I would ...

One cell from several sheets to one column
I have a spreadsheet with over 200 tabs/worksheets. I want to copy the same cell from every worksheet into one sheet into a single column. The worksheets have unique names. Is a macro the easiest way to move the data? If so, how can I set the macro up to go through each tab, so I don't have to go to each tab and run the macro separately? Thanks, Kel Examples of sheet names and what cell you want? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------------------------------------...

make row height a little bigger in a wrapped cell
I've got a sheet with columns at work date text (sometimes multi-line wrapped) $ nnnn.nn I can wrap text no problem but I hope approximate double spacing so I don't have to put a blank row between each item. I need the space between items so the text isn't squashed all together for a court form I guess I want to be able to say height of cell necessary for the wrapped text however much it might be plus n for each row height. I can't find a way. Is it possible? Excel 2002 Win xp Thanks for your help. If you're using merged cells, then this w...

Countif for finding a Time input
Got a big long column D containing times formatted as i.e. 13:30. I am trying to do a countif function for finding the count of inputs between 6 am and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of using logic => and < within the countif formula... appreciate any help here.. Hi, maybe this for 06:00 to 06:59:59 =SUMPRODUCT((D1:D8>=TIME(6,0,0))*(D1:D8<TIME(7,0,0))) or you can do this for 06:00 to 06:59:59 and drag down for subsequent hours =SUMPRODUCT((D1:D8>=TIME(ROW(A6),0,0))*(D1:D8<TIME(ROW(A7),0,0))) Mike When comp...

Split Cell into the cell
Hello people how can i split a cell in half A 1 Tom 2 dick/harry (break dick and harry) Thanks Raj To separate the two into two different columns, try......... Data > TextToColumns > using / as the delimiter.......... Vaya con Dios, Chuck, CABGx3 "Raj" <Raj@discussions.microsoft.com> wrote in message news:159CD0DF-13E0-40A9-8CDA-3258B842BF34@microsoft.com... > Hello people how can i split a cell in half > > A > 1 Tom > 2 dick/harry (break dick and harry) > > Thanks > Raj ...

coloring cells
I want to alternate colors between successive rows. One blue one white, the next one blue and the next white and so on to the end of my worksheet. It there a easier way than to hold the control key and click on each row? Question # 2 when I sort will the colors stay with the row or will they move with the sort. Is there a way to get the colors to stay even when I sort? You can use conditional formatting which applies the colour using the Mod() function: http://www.ozgrid.com/Excel/alternate-row-color.htm After sorting, every second row remains the same colour as before sorting. ...

automatic name finding in To: box feature
I can't find out how to make my Outlook 2000 program automatically fill the names I type in the To: box. I know how to do it in Outlook XP, but can't find it in Outlook 2000. Is there a Help Book that I can download for Office 2000 or just Outlook 2000? Outlook 2000 doesn't have an AutoComplete feature -- it uses Auto Resolution instead. See the following article for a description of how this works: http://www.slipstick.com/emo/2000/up001108.htm#autocomplete -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only ...

Query for merge column
Dear Friends Please see it I want query Table name emp A B C D vivek vinod randhir Delhi vivek vinod randhir patna vivek vinod randhir Kolkata vivek vinod randhir Madras I want record like Vivek vinod randhir Delhi,patna,Kolkata,Madras Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601...

Finding current printer page number
I have hunted for this without success. I want to know - programmatically - what page the active cell is in. More precisely, what page ActiveCell.Top is in. (I take it some sows can split into more than one page.) I want to automate putting manual page breaks into a sheet. I would put breaks at or before those automatically inserted by Excel. Thanks -- Walter Briscoe In message <KBH7nVN7dSuJFwvS@freenetname.co.uk> of Thu, 12 Mar 2009 15:17:15 in microsoft.public.excel.newusers, Walter Briscoe <wbriscoe@nospam.demon.co.uk> writes >I have hunted for this without success. >...

Chart should read as blank cell not as 0,it shows blank but reads
A B c 1 Project Start Date Jan-10 2 Project Duration 25 3 Project Cost 1000000 In B6 i wriiten formula IF(ROWS($1:2)-1>C$2,"",ROWS($1:2)-1) In D6 i written formula IF(B6="","",B6/$C$2) in E6 i writtend formula IF(D6<=33.33%,(($I$1*D6^2)*100),IF(D6<=66.67%,($I$2*D6+$I$3),IF(D6>=66.67%,($I$4+$I$5*D6+$I$6*...

Autoincrementing cell in Template
I am currently creating a template. My final stumbling block is creating an auto incrementing cell in the template. What I want to happen is that every time the template is opened, the next sequential number fills in the cell. The format I am looking for is basically yy-#####. For example, the first time it is used this year, it would assign the cell to 04-00001, followed by 04-00002, etc. I am lost on how to do this. Any ideas? Thanks, Andy -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Hi one way (using the Windows registry for storing the last ...

Merge worksheets #2
Hello, I have two worksheets in "list" format. I would like to merge these two list into a third worksheet if possible.. If not possible merging into one of the original worksheets is ok. Both of these tables "lists" are sequenced by the first column (a text field containing a telephone number). List one is the master list because all rows i.e. telephone numbers will be on this list(sorted in ascending order based on telephone number). The second list(called the update list) will be identical to the first also sorted into ascending order. However it may only have only a...

Copying spinners for each cell in Excel 2003?
I have an inventory tracking sheet that keeps track of my inventory. I've added in a spinner control to increase/decrease a value in a certain cell. In my worksheet, the value is located in column E, and the spinner control is in column F. I have about 200 spinners to add, and need to fill down the links to it's adjacent cell. I found something related to checkboxes, but how do I adapt it for the spinner control? The first value is in E3, and the first spinner is in F3. Create 200 spiners in F3:F203 and link to E3:E203 Sub add_spinners_and_links() Dim myRng As Range Dim Spnr...

IF COUNTIF & COUNTA on Filtered Visible Cells
Hi, Using the Formulae below, I can obtain the required data from the Columns in a non-filtered state. Column T: =IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U11+1&":V"&ROW()))-1,COUNTA($V$10:$V10)) Column U (helper column): =MAX(IF($V$10:$V10=$V11,ROW($V$10:$V10))) Column V: Text Data However, I also need to retrieve filtered data, and so require Formulae that will take into account Filtered Visible Cells and not the Non-Filtered data in the columns. Can the formulae be adapted to work on Filtered Visible Cells. Thanks Tin� -- Tin� --------------...

Hyperlink to cell within the same Workbook
I Have a workbook with about 30 sheets, I named cells contained essential information. With so many sheets I decided to make another one and I call it Summary. In this sheet I created a search with 3 droplists that are linked to functions. Know I need make a link to the cell that contains the result of my search, so I can see the items that have contributed for the result. I have tried the funtion "Hyperlink" but it only works with files or url and I can't make it work within the workbook. How can I make a hyperlink within an workbook? Make an hyperlink to a cell in a...

Specialized Cell Formatting
I have a column that contains a string of numbers, but I need to have it formatted in a customized way using a few .'s. And I want to know if there is any way I can set the formatting for this column so that it will automatically change all the cells in this column without me having ot manually do it. Here's an example of what I need: In each column a string of numbers like: 123456789012345 The string of numbers need to be formatted like so: 123456.78.901234.5 The column is already filled with numbers in the first example. Can I somehow automate the formatting of this string of nu...

Merge credit card accounts?
This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C6A5E1.00148FD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Money 2006. How can I merge two credit card accounts. When I use merge = accounts, the only choice I have is to merge my pay pal account with an = existing bank account. My problem is this I have a Sears credit card = account that I have entered manually for several years. It has a record = of all my purchases. When I set up money on line for searscard, it just = shows the last month purchases. I would l...

Auto height rows
I am working in Excel 2000 for Windows with cells that are NOT merged. The individual cells have varying amounts of data (anywhere from a few characters to over 1000 characters). All text is wrapped. When I double click on the row to auto height, it adjusts the height but the printed version still cuts off text. In most cases the row height does not come anywhere near 409.5 pixels. I saw the problem with bottom aligned text, but all my cells are top aligned. I am working with 19 files that include at least four worksheets with 300+ rows of data. It's getting time consuming to re...

sheet tabs as page number and in a cell page of pages?
i have looked a little but can't find what i think is the right thing. i have a workbook with 3-4 pages in it. in the upper righthand corner is a page of pages cells set up. i want to have it grab the current tab(page) for one cell and the rest of the tabs(pages) for the other cell. i am not very good at the coding VB thing so exact instructions would be grately appreciated. i have tried a few of the suggestions but cannot get them to work. i am using excel 2002. TIA ...

catalog merge #7
OK, I have a lot of genealogical images that I have cataloged in an Access database. I've successfully merged them in Publisher 2003 and created a catalog that shows the photo with all the related detail. My question is: now that I've created this merge, I would like to add more images and information to the database. Can I use the design of the catalog I've already created to make a new catalog - such as in a template, or do I have to use the wizard to completely re-create a new catalog design every time I change the database? Sure you can, when you open the catalog wizard,...

Javascript to retrieve Gridview cell text
This is a Label in ItemTemplate i wish to use javascript to put <%# Eval("Organization") %> in a textbox How can i take the text value of <%# Eval("Organization") %>? put this - onmousedown="alert(<%# Eval("�ӽо��c") %>)" ??? thanks for any ideas? *********************** <asp:Label ID="Label11" runat="server" Text='<%# Eval("Organization") %>' ></asp:Label> &nbsp;<asp:Label ID="Label2" runat="server" Text='<%# Eval("Depart...

Duplicate Cells
I have a very large, 2 column spread sheet. Every other row is a duplicate. How can i, easily delete the duplicate rows and/or cells? sorry i made a mistake. it isn't work. >-----Original Message----- >I have a very large, 2 column spread sheet. Every other >row is a duplicate. How can i, easily delete the duplicate >rows and/or cells? >. > Lanny Assuming that it is every other row you want to keep, use a column alongside your data and put =MOD(ROW(),2) and fill this down. Select the column of results, Copy, Paste Special/Values. You can now sort on this column...

Forms Check Box cell link
Excel 2003 I am using a check box created with the Forms menu. I created a box over cell D4. Using the Format Control box, I linked the check box to cell D4. I want to copy the check box in cell D5, D6, D7,.... and I want the cell link to integrate in the same mannor; check box in cell D5 should link to cell D5, and so on. Is there a way to accomplish this short of going into the format control box and manually editing each cell link after I have copy and pasted several check boxes? I have NOT entered the link using an absolute cell referense. I got the idea for this from Dav...

clearing a cell
I'm trying to put the date in a single cell but another number is popping up when i click enter. How can I get the date to stay and not that other number? What date and what number? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------�------------------------------�---------------- "ahegg" <ahegg@discussions.microsoft.com> wro...