|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
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 Code:
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 |
#2
|
||||
|
||||
Assuming this relates to userform code:
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Sir,
Quote:
Thank you so much you made me remove the following Code:
NonEmptyCellListStr = NonEmptyCellListStr & .RowIndex & ", " & .ColumnIndex & "^" NonEmptyCellsList = Split((Replace(NonEmptyCellListStr, Chr(147), "")), "^") Nevertheless Inorder to have correct neat representation for Cell Reference uploaded in Combobox Quote:
Code:
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 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 |
#4
|
||||
|
||||
If you want the empty cells listing then
Code:
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to SUM the values only if the particular cells are not empty? | Learner7 | Excel | 3 | 11-10-2020 02:47 AM |
auto progress number evenly between two values across empty cells ? | DBenz | Excel | 4 | 03-28-2018 04:46 AM |
If a2 is not empty, color empty cells in b2:af2 | turkanet | Excel | 2 | 08-20-2017 11:00 PM |
Microsoft Excel 12.0 Object Library reference upload | chamdan | Project | 2 | 04-21-2014 06:30 AM |
Apparently empty (blank) cells aren't empty | daymaker | Excel | 3 | 03-08-2012 03:41 PM |