Geek Answers Handbook

How to find start and end rows of merged cells in Excel using VBSCript?

From a VBS script I need to work with an Excel workbook in 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 categories of 1st level and 2nd level (folders / items), and columns B and C contain data for the items. The problem is that a single element can span multiple lines, as shown; Element 1 is a merged cell from rows 3 through 11).

I need to create .csv from this data, which looks like this:

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

The data in column C may be discarded.

Basically, I need to know how to determine if a cell is merged (i.e. more than one row of information for each element) and how many rows are merged. Any idea?

+5
3

:

if (Cell.MergeCells) Then ...

, ,

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

, .

, . CSV:

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
+10

. eJames , , , , FolderName/ItemName. .

CSV , , FOLDERn (A-C), , , Mister Lucky:

mergedColumns = cellRange.MergeArea.Columns.Count

mergedColumns > 1, .

+2

, :

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

More articles:


All Articles