vba table valid row count

Brass Contributor

Hi,

I'm supplied Word documents that contain a table that has differing valid rows therefore I need a variable to count the valid rows.
In Excel this would be something like:

Sub Test()
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
    End With
End Sub

 I can use the following within word vba to count rows excluding the header row but I don't appear to be able to get .End(xlUP)

Dim row As Integer

Dim excelApp As Object

Set excelApp = CreateObject("Excel.Application")

row = excelApp.Worksheetfunction.Clean(ActiveDocument.Tables(1).UsedRange.Rows.Count

 

Any ideas how I can count valid rows?

 

TIA

 

10 Replies

@a_bonsey 

Use 

Dim objSS As InlineShape
With ActiveDocument.InlineShapes(1)
    .OLEFormat.DoVerb wdOLEVerbHide
    With .OLEFormat.Object.Application
        With .workbooks(1).Worksheets(1)
            MsgBox .Range("A1").currentregion.Rows.Count
        End With
    End With
End With
Thanks for that response.
I tried running and go the error message 'the requested member of the collection does not exist'
The table is there and editable so any ideas why it errors?

TIA

@a_bonsey Is a line of the code highlighted when it fails?

 

This is the result of running the code here

 

Doug_Robbins_Word_MVP_0-1652786738887.png

 

@Doug_Robbins_Word_MVP 

See attached (showing table data too).

Hope that helps

 

a_bonsey_0-1652793636762.png

 

@a_bonsey Can you upload a copy of the document

@Doug_Robbins_Word_MVP 

Sure. Here you go

@a_bonsey That document contains a Word table, not an Excel worksheet.

 

The following macro will tell you how many rows contain data:

With ActiveDocument.Tables(1)
    For i = 1 To .Rows.Count
        If Len(.Cell(i, 1).Range) < 3 Then
            Exit For
        End If
    Next i
End With
MsgBox "The table contains data in " & i - 1 & " rows, including the header row."

@Doug_Robbins_Word_MVP 

That worked brilliantly and looked so obvious when you laid it out like that.

Just couldn't see the woods for the trees as they say!! :xd:

 

Thanks again

@Doug_Robbins_Word_MVP 

 

Hi,

I tried tweaking the vba slightly to see what a debug/msgbox would should for the cell(i,1). In the debug (and Msgbox) I get a 'rectangular box' appearing (see attached image). 

 

Are you able to tell me what causes this to appear?

 

 

@a_bonsey The range of an empty cell in a Word table has a length of 2.  Therefore, the code checks to see if the length of the range of each cell is < 3.  If it is, then the row is not valid according to your nomenclature.

In the Immediate Window, you should enter

MsgBox Len(Selection.Cells(1).Range)

when the selection is in a cell.