How can i upload values of Non-Empty Cells Reference of a Table in ComboBox

[SamDsouza]

Hello

Any ideas How can i upload values of ONLY Non-Empty Cells Reference of a Table in ComboBox. Bit Confused in getting the right syntax.

The below code gives all the Cell References in the table. Empty and Non-Empty

Public Sub NonEmptyCellRef()

Dim oTbl As Table
Dim NonEmptyCellListStr As String,  NonEmptyCellsList As Variant

Dim wdActDoc As Document
Set wdActDoc = ActiveDocument


Set oTbl = wdActDoc.Tables(1)
With oTbl.Range
  For i = 1 To .Cells.Count
    With .Cells(i)
          NonEmptyCellListStr = NonEmptyCellListStr & .RowIndex & ", " & .ColumnIndex & "^"  
          NonEmptyCellsList =  Split((Replace(NonEmptyCellListStr, Chr(147), "")), "^")
    End With
  Next
  ComboBox1.List = NonEmptyCellsList

End With

End Sub

SamD

[gmayor]

Assuming this relates to userform code:

Private Sub UserForm_Initialize()
    NonEmptyCellRef
End Sub

Private Sub NonEmptyCellRef()
Dim oTbl As Table
Dim i As Integer
    Set oTbl = ActiveDocument.Tables(1)
    With oTbl.Range
        For i = 1 To .Cells.Count
            If Len(.Cells(i).Range) > 2 Then
                ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex & "^"
            End If
        Next i
    End With
End Sub

[SamDsouza]

Sir,

Assuming this relates to userform code:

Yes Mostly i use Userform Objects

Thank you so much you made me remove the following

NonEmptyCellListStr = NonEmptyCellListStr & .RowIndex & ", " & .ColumnIndex & "^" 
NonEmptyCellsList =  Split((Replace(NonEmptyCellListStr, Chr(147), "")), "^")

OMG And How could I forget Combobox1.Additem

Nevertheless Inorder to have correct neat representation for Cell Reference uploaded in Combobox

i changed
ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex & "^"
to
ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex

As I moved on to check Now for Empty Cell Reference with following code. Some unusual results

Private Sub EmptyCellRef()
Dim oTbl As Table
Dim i As Integer, sText As String, sList As String
    Set oTbl = ActiveDocument.Tables(1)
    With oTbl.Range
        For i = 1 To .Cells.Count
             sText = Trim(.Cells(i).Range.Text)
             If Split(sText, vbCr)(0) = "" Then                
                   ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex 
            End If
        Next i
    End With
End Sub

I dont understand why the result is different. Have Attached .docx File

I get the List of Empty Cell Reference as

2, 2
2, 3
3, 1
3, 2
3, 3
4, 1
4, 2
4, 3

All though some of the above cell reference has some data. like cells 2, 2 and 2, 3

To have correct list of Empty Cell Reference as mentioned below
3, 1
3, 2
3, 3
4, 1
4, 2
4, 3

Do I have to consider the Paragraphs Aspect of Each cell. if yes then How ? to get the above mentioned Empty Cell Reference

SamD

[gmayor]

If you want the empty cells listing then

For i = 1 To .Cells.Count
            If Len(.Cells(i).Range) = 2 Then
                ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex
            End If
Next i

[SamDsouza]

Thank you Sir,


What i take from your posts of this thread is that minimum length of Cell of Table is 2.
Any specific reason for MINIMUM Len of Cell of a Table = 2

Also I need to take care whenever i have to get Len of Text in a cell will always be as LEN(.Cell(1,1).Range.Text)- 2

This is RESOLVED


SamD