CODEWITHSUNDEEP
excelms-wordlogicbordervba
ssoong
ssoong

Reputation: 167

Excel VBA - Detecting cell borders or alternate solutions

I am having a logic issue and I think the only way to deal with it is by detecting cell borders. Is there a way to do this in Excel VBA?

background

I am working on a project that pastes a Word table into an excel sheet. However, I have found that line breaks in the Word table separate the text into separate cells merged together. VBA does not like merged cells so I've added a line of code to unmerge all cells in the sheet. The result is that some cells from the Word table have been spread out over multiple cells, adding new rows here and there. I've been writing VBA code to consolidate the separated cells back into one using a one line "ID" column key from the Word document as a marker to section off the actual table cells.

the issue

When I paste a table with another table nested within a cell, it adds a number columns and rows corresponding to that inner table. I can't add images yet, so i'll draw it out here (sorry). Each separate line is a new row.

"Header1"                    l    "No Header"            l   "Header2"
"Cell text prior to table"   l    "(blank)"              l
"Table (1,1) text"           l    "Table (1,2) text"     l
 Problem text 1              l    Problem text 2         l
"Table (2,1) text"           l    "Table (2,2) text"     l
"Cell text after table"      l    "(blank)"              l

Edit: I should clarify that I want the consolidated cell to show

Cell text prior to table

Table (1,1) contents Table (1,2) contents

Table (2,1) contents Table (2,2) contents

Cell text after table

Etc...

I cannot identify whether or not "Problem text 1" and "Problem text 2" were originally part of (1,1) and (1,2) separated by a line break, or if they are an entire new row in the table. The only way i can think of is to identify cell borders, (as the formatting for the nested table is carried over from word) and write a million nested "if clauses" to identify where cells need to have their values added.

Upvotes: 0

Views: 1044

Answers (1)

ssoong
ssoong

Reputation: 167

I did it with

If RCon.Offset(columnOffset:=-1).Borders(xlEdgeBottom).LineStyle <> xlNone Then
ElseIf RCon.Offset(columnOffset:=-1).Borders(xlEdgeBottom).LineStyle = xlNone Then
       For Each RCon2 In .Range(.Cells(RCon.Offset(1).Row, CCount.Offset(columnOffset:=-1).Column), .Cells(.Cells(Rows.count, CCount.Offset(columnOffset:=-1).Column).End(xlUp).Row, CCount.Offset(columnOffset:=-1).Column))
              If RCon2.Borders(xlEdgeBottom).LineStyle <> xlNone Then
                    If RCon2.Value <> "" And RCon2.Value <> 0 Then
                          RCon.Offset(columnOffset:=-1).Value = RCon.Offset(columnOffset:=-1).Value & vbNewLine & RCon2.Value
                          RCon2.ClearContents
                    End If
                    Exit For
              ElseIf RCon2.Borders(xlEdgeBottom).LineStyle = xlNone And RCon2.Value <> "" And RCon2.Value <> 0 Then
                    RCon.Offset(columnOffset:=-1).Value = RCon.Offset(columnOffset:=-1).Value & vbNewLine & RCon2.Value
                    RCon2.ClearContents
              End If
       Next RCon2
End If

Upvotes: 0

Related Questions