Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2021, 10:12 PM
SamDsouza SamDsouza is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2013
Advanced Beginner
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
 
Join Date: Aug 2019
Posts: 71
SamDsouza is on a distinguished road
Default 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
SamD
Reply With Quote
  #2  
Old 03-07-2021, 11:59 PM
gmayor's Avatar
gmayor gmayor is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,568
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 03-08-2021, 04:26 AM
SamDsouza SamDsouza is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2013
Advanced Beginner
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
 
Join Date: Aug 2019
Posts: 71
SamDsouza is on a distinguished road
Default

Sir,
Quote:
Assuming this relates to userform code:
Yes Mostly i use Userform Objects

Thank you so much you made me remove the following
Code:
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
Quote:
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
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 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
Attached Files
File Type: docx Table-Cells-NonEmpty-Empty-Reference.docx (11.5 KB, 1 views)
Reply With Quote
  #4  
Old 03-08-2021, 04:53 AM
gmayor's Avatar
gmayor gmayor is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,568
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 03-08-2021, 10:00 PM
SamDsouza SamDsouza is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2013
Advanced Beginner
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
 
Join Date: Aug 2019
Posts: 71
SamDsouza is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox 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
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:46 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft