Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to detect merged cells with C#
Hi,
I do Excel programming first time, and I create a program to detect merged cells , by this way: 1.Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); 2.Get every cells in the worksheet, and check it is merged cell, or not. €»There is one worksheet in the Excel only. but the worksheet has 60 columns and 10000 rows. and it take long time to finish detecting a worksheet. Is there any better way to do it? Best, Mars |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to detect merged cells with C#
What version of excel are you supporting?
xl2002 (it's there in xl2003 for sure!) added an option to search by format. Before excel offered that Edit|find option, the only way I know is to loop through the .usedrange. Try this manually in excel. Start a new workbook. Merge a few cells (mark them nicely with a fill color???). Then do Edit|find Click on the Options button if you don't see them. Click on the Format button Remove everything that you don't want, but make sure "merge" is selected on the Alignment tab. Then you could use Find in your code to look for just merged cells. If I remember correctly, VBA's .FindNext doesn't remember the format settings, so you'll have to use successive .Find's. In excel's VBA, I'd use: Option Explicit Sub testme() Dim wks As Worksheet Dim myMergedCells As Range Dim FoundCell As Range Dim FirstAddress As String Set wks = ActiveSheet With wks 'clear any existing formatting that was used Application.FindFormat.Clear 'just the merged cells With Application.FindFormat .MergeCells = True End With Set FoundCell = .Cells.Find(What:="", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=True) If FoundCell Is Nothing Then 'not one unlocked cell! Else 'keep track of where the first one was, so we can quit 'when we find this again. FirstAddress = FoundCell.Address 'start building the range of unlocked cells Set myMergedCells = FoundCell Do Set FoundCell = .Cells.Find(What:="", _ After:=FoundCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=True) If FoundCell Is Nothing Then Exit Do End If If FoundCell.Address = FirstAddress Then Exit Do End If 'add to the growing range Set myMergedCells = Union(myMergedCells, FoundCell) Loop End If End With If myMergedCells Is Nothing Then MsgBox "None found!" Else Application.Goto myMergedCells ', scroll:=True MsgBox myMergedCells.Address End If End Sub Mars wrote: Hi, I do Excel programming first time, and I create a program to detect merged cells , by this way: 1.Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); 2.Get every cells in the worksheet, and check it is merged cell, or not. €»There is one worksheet in the Excel only. but the worksheet has 60 columns and 10000 rows. and it take long time to finish detecting a worksheet. Is there any better way to do it? Best, Mars -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with pasting special merged cells to merged cells | Excel Programming | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions | |||
VBA: How to detect whether two cells are currently merged | Excel Programming |