none
Assigning to Range.Formula or Range.Value2 duplicates assignment to all cells below assigned cell - in table RRS feed

Answers

  • Excel automatically extends formulas in a table cell to the entire table column, to ensure that a table works consistently.

    To avoid this behavior, convert the table to a range, using the Unlist method of the ListObject.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by DavidThi808 Sunday, November 22, 2020 11:48 AM
    Saturday, November 21, 2020 4:56 PM
  • Here is an example:

    Sub Test()
        Dim rng As Range
        Dim tbl As ListObject
        Set rng = Range("B9")
        On Error Resume Next
        Set tbl = rng.ListObject
        On Error GoTo 0
        If Not tbl Is Nothing Then
            tbl.Unlist
        End If
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by DavidThi808 Sunday, November 22, 2020 11:48 AM
    Saturday, November 21, 2020 8:18 PM

All replies

  • Excel automatically extends formulas in a table cell to the entire table column, to ensure that a table works consistently.

    To avoid this behavior, convert the table to a range, using the Unlist method of the ListObject.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by DavidThi808 Sunday, November 22, 2020 11:48 AM
    Saturday, November 21, 2020 4:56 PM
  • Hi;

    Thank you. A follow on question please. The object I have is the selected Cell and from that the Range that is that cell.

    From that, how do I determine I'm in a table, then in the table how do I get the ListObject of the table and then Unlist() it? And do so so I am left with the selected cell (might not be the first cell)?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Saturday, November 21, 2020 6:35 PM
  • Here is an example:

    Sub Test()
        Dim rng As Range
        Dim tbl As ListObject
        Set rng = Range("B9")
        On Error Resume Next
        Set tbl = rng.ListObject
        On Error GoTo 0
        If Not tbl Is Nothing Then
            tbl.Unlist
        End If
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by DavidThi808 Sunday, November 22, 2020 11:48 AM
    Saturday, November 21, 2020 8:18 PM
  • thank you, thank you, thank you!

    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Sunday, November 22, 2020 11:48 AM