CODEWITHSUNDEEP
excelvbacountrows
T. Coates
T. Coates

Reputation: 19

Why does MyTable.Range.Rows.Count return an incorrect value in Excel VBA

I've been working on a problem for a few days now and have almost arrived at the solution. I have been currently working on an excel application where I have a macro, operated by a button click, which resets the numerical values within certain cells in a table.

Within this table there are 3 columns; "Quantity Fitted (n)", "Quantity Required (m)" and "Lock Configuration".

What I need to happen, is when the button is clicked, the numerical values for each line within the "Quantity Fitted (n)" column are reset to match the values displayed within the "Quantity Required (m)" column of the same line.

However, if the cell value within the "Lock Configuration" column of that row is set to "locked", I want the "Quantity Fitted (n)" value to remain unchanged after the button click.

I hope this makes sense! Here's my current attempt to code this:

Dim x As Long

    'Set reference to your table.  Have hard-coded the sheet name and table name in.
    Dim MyTable As ListObject
    Set MyTable = Worksheets("System").ListObjects("Table_System")

    'These will be the numerical index numbers of the columns in your table.
    'This assumes your "Locked" column is in the table and has the header "Locked".
    Dim Required As Long, Fitted As Long, Locked As Long
    Required = MyTable.ListColumns("Quantity Required (m)").Index
    Fitted = MyTable.ListColumns("Quantity Fitted (n)").Index
    Locked = MyTable.ListColumns("Lock Configuration").Index

    'Look at each row in the table.  Am using `x` rather than `Each Cell`
    'as the row number of the cell may differ from the row location in the table
    'e.g. If your table starts on row 2, then first row after the header is row 3 - row 3 as Cell, but row 1 in table.
    For x = 1 To MyTable.Range.Rows.Count
        'If Locked column doesn't say "Locked" then copy value from
        'Fitted to Required columns, otherwise do nothing.
        If MyTable.DataBodyRange.Cells(x, Locked) <> "Locked" Then
            MyTable.DataBodyRange.Cells(x, Fitted) = MyTable.DataBodyRange.Cells(x, Required)
        Else
            'Do Nothing.
            'The ELSE and comment aren't really needed - just here to show nothing happens.
        End If
    Next x

End Sub

This achieves the aim, however the code breaks down due to an incorrect count of the rows within the table. There are 3 rows within my table, and by typing the formula directly into the excel sheet of =Rows(Table_System) the value of 3 is returned.

However, within my code, I can see by declaring a new variable Dim y As Long and setting it so y = MyTable.Range.Rows.Count that the value for y being returned is 5.

Similarly, if I increased the rows in my table by 1, the excel formula updates its solution to 4. However, the y value within my code now displays 6. It's always +2 ahead? Why would this be?

Thank you!

Upvotes: 0

Views: 1927

Answers (1)

Tim Williams
Tim Williams

Reputation: 166850

MyTable.Range

includes column header row and any footer (eg totals) row you might have

MyTable.DataBodyRange

includes only the data rows. So this should work:

For x = 1 To MyTable.DataBodyRange.Rows.Count

Upvotes: 2

Related Questions