How do I find start and end rows of merged cells in Excel with VBSCript?

Question

From a VBS script I have to work with an Excel book with the format:

    |     A     |     B     |     C     |
----|-----------|-----------|-----------|
  1 |  Header1  |  Header2  |  Header3  |
----|-----------|-----------|-----------|
  2 |  FOLDER1  |           |           |
----|-----------|-----------|-----------|
  3 |   Item1   |    111    |    222    |
----|           |-----------|-----------|
  4 |           | Item1Info | Item1Data |
----|           |-----------|-----------|
 .. |           |    ...    |    ...    |
----|           |-----------|-----------|
 11 |           |    333    |    444    |
----|-----------|-----------|-----------|
 12 |   Item2   |    555    |    666    |
----|-----------|-----------|-----------|
 13 |  FOLDER2  |           |           |
----|-----------|-----------|-----------|
 14 |   Item1   |    777    |    888    |
----|-----------|-----------|-----------|
 .. |    ...    |    ...    |    ...    |

So: Column A has 1st level and 2nd level categories (Folders/Items), and Columns B and C hold the data for the items. The trouble here is that one item can span several rows, as shown; Item1 is a merged cell from rows 3 to 11).

I need to create a .csv from this data that looks as follows:

Header1,Header2,Header3
111,Item1,FOLDER1
Item1Info,Item1,FOLDER1
...
555,Item2,FOLDER1
777,Item1,FOLDER2
...

The data in Column C can be discarded.

Basically, what I need to know is how to detect if a cell is merged (that is; more than one row of information per item) and how many rows are merged together. Any idea?

Answer 1

You can use:

if (Cell.MergeCells) Then ...

to determine if a cell is part of a merged range, and

Cell.MergeArea.Cells(1,1).value

to access the value contained in that merged range.

Here is some sample code to get you started. I have omitted the details of actually writing the CSV file:

Public Sub MakeCSV()
    Dim rowIndex As Integer
    Dim itemColumn As Range
    Dim dataColumn As Range
    Dim itemCell As Range
    Dim FolderName As String
    Dim ItemName As String
    Dim CSVLine As String

    Set itemColumn = Range("A2:A100")
    Set dataColumn = Range("B2:B100")

    For rowIndex = 1 To dataColumn.Rows.Count
        If dataColumn.Cells(rowIndex, 1).value = "" Then
            Rem // determine the current folder name
            FolderName = itemColumn.Cells(rowIndex, 1).value
        Else
            Rem // determine the item name (accounting for merged cells)
            Set itemCell = itemColumn.Cells(rowIndex, 1)
            If itemCell.MergeCells Then
                ItemName = itemCell.MergeArea.Cells(1, 1).value
            Else
                ItemName = itemCell.value
            End If

            Rem // write a line to the CSV file
            CSVLine = dataColumn.Cells(rowIndex, 1).value
            CSVLine = CSVLine & "," & ItemName
            CSVLine = CSVLine & "," & FolderName
        End If
    Next rowIndex
End Sub

Answer 2

To test if merged cells cover more then one Row:

cellRange = ExcelApplication.Cells(rowIndex, columnIndex)    
mergedColumns = cellRange.MergeArea.Rows.Count
if mergedColumns > 1 then ' merged

Answer 3

Thanks for your responses. eJames I think you have answered everything I wanted to know, how to detect merged cells and also the FolderName / ItemName assigning. Thanks again.

I can write the CSV with no problem, upon closer inspection I noticed that also the FOLDERn cells merge columns (A-C) so probably I can also check with Mister Lucky code:

mergedColumns = cellRange.MergeArea.Columns.Count

And if mergedColumns > 1 then it's a folder name.