Add new data entry in a new row under existing row in middle of table

%3CLINGO-SUB%20id%3D%22lingo-sub-1943760%22%20slang%3D%22en-US%22%3EAdd%20new%20data%20entry%20in%20a%20new%20row%20under%20existing%20row%20in%20middle%20of%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1943760%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20repeating%20entries%20in%20column%201.%20i%20have%20a%20button%20which%20adds%20data%20adjacent%20to%20each%20match%20of%20column%201.%26nbsp%3B%20this%20works%20thanks%2C%26nbsp%3B%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F234290%22%20target%3D%22_blank%22%3E%40Craig%20Hatmaker%3C%2FA%3E%20you%20are%20amazing.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20already%20data%20in%20the%20adjacent%20columns%20and%20i%20want%20to%20add%20a%20new%20data%20entry%20for%20the%20same%20match%20then%20i%20would%20like%20the%20information%20displayed%20in%20a%20row%20beneath%20the%20row%20with%20data(leaving%20a%20blank%20space%20below%20the%20first%20column%20match%20followed%20by%20the%20data%20in%20column%202%20and%203).%20this%20is%20as%20a%20pose%20to%20overwriting%20the%20data%20in%20the%20adjacent%20cells%20which%20is%20what%20happens%20currently.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20made%20an%20attempt%20and%20a%20new%20row%20is%20entered%20but%20i%20cannot%20get%20the%20row%20to%20be%20entered%20at%20the%20right%20place.%20also%2C%20i%20made%20an%20error%20in%20syntax%20for%20calling%20the%20table%20to%20have%20a%20row%20added%20to%20it%2C%20any%20help%20is%20greatly%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1943760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1944829%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20new%20data%20entry%20in%20a%20new%20row%20under%20existing%20row%20in%20middle%20of%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871583%22%20target%3D%22_blank%22%3E%40Linesol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20kind%20words.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20trying%20to%20solve%20the%20problem%2C%20let's%20learn%20about%20tables%20in%20VBA.%20In%20VBA%20they%20are%20ListObjects.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDim%20oLo%20as%20ListObject%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EGetting%20(Setting)%26nbsp%3BListObjects%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EListObjects%20belong%20to%20the%20worksheet's%20ListObjects%20collection.%20We%20can%20select%20the%20table%20from%20the%20worksheet's%20ListObjects%20collection%20in%20several%20ways.%20Here%20are%203.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESet%20oLo%20%3D%20ThisWorkbook.Sheets(%22Sheet1%22).ListObjects(1)%0A%0A'%20or%0A%0ASet%20oLo%20%3D%20Sheet1.ListObjects(1)%0A%0A'%20or%0A%0ASet%20oLo%20%3D%20Sheet1.ListObjects(%22Table1%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20because%20a%20ListObject%20is%20an%20object%20we%20must%20use%20%3CSTRONG%3ESet%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20can%20also%20select%20a%20table%20by%20through%20a%26nbsp%3Brange's%20ListObject%20property.%20Any%20cell%20occupied%20by%20the%20table%20will%20return%20the%20ListObject%20like%20so%20(Assume%20A5%20is%20in%20the%20table)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESet%20oLo%20%3D%20Sheet1.Range(%22A5%22).ListObject%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20that%20we%20know%20ranges%20occupied%20by%20ListObjects%20can%20return%20the%20ListObject%2C%20we%20can%20leverage%20my%20preferred%20method%3A%20getting%20a%20ListObject%20by%20Table%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20table%20names%20MUST%20be%20unique%20within%20a%20workbook.%20Excel%20enforces%20this%20rule%20by%20tracking%20table%20names%20in%20its%20names%20collection%2C%20but%20the%20name%20does%20not%20refer%20to%20the%20ListObject%2C%26nbsp%3BIt%20refers%20to%20the%20table's%20range.%20Another%20odd%20note%20is%20the%20table%20name%20is%20not%20accessible%20from%20the%20workbook's%20names%20collection%20directly.%20We%20must%20use%20VBA's%20EVALUATE%20method%20to%20evaluate%20the%20name%20and%20return%20its%20range.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESet%20oLo%20%3D%20EVALUATE(%22Table1%22).ListObject%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20above%2C%20we%20evaluated%20the%20name%20%22Table1%22%20which%20returned%20the%20range%20that%20Table1%20occupies%20and%20that%20range%20has%20a%20ListObject%20property%20that%20returns%20the%20ListObject%20in%20those%20cells.%26nbsp%3B%20Lastly%2C%20there%20is%20an%20abbreviation%20for%20EVALUATE%20which%20is%20%5B%20%5D.%20This%20is%20how%20I%20get%20ListObjects%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESet%20oLo%20%3D%20%5BTable1%5D.ListObject%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EListObject%20Properties%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EOne%20of%20the%20wonderful%20things%20about%20tables%20is%20they%20expand.%20That%20means%20Excel%20must%20keep%20track%20of%20how%20many%20rows%20and%20columns%20a%20ListObject%20has.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDim%20lRow%20as%20Long%0ADim%20lCol%20as%20Long%0A%0AlRow%20%3D%20oLo.ListRows.Count%20%0AlCol%20%3D%20oLo.ListColumns.Count%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThus%2C%20we%20can%20loop%20through%20all%20table%20rows%20like%20so%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFor%20lRow%20%3D%201%20to%20oLo.ListRows.Count%20%0A...%0ANext%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20because%20the%20ListObject's%20%3CSTRONG%3EListRows%20%3C%2FSTRONG%3Eis%20a%20collection%20of%20%3CSTRONG%3EListRow%3C%2FSTRONG%3E%20objects%2C%20we%20can%20loop%20through%20it%20using%20VBA's%20FOR%2FEACH%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDim%20oLr%20as%20ListRow%0A%0AFor%20Each%20oLr%20in%20oLo.ListRows%0A...%0ANext%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20ListRow%20has%20an%20Index%20(row%20%23)%20and%20a%20collection%20of%20cells.%20Thus%20we%20can%20set%26nbsp%3Bthe%26nbsp%3Bcells%20in%20your%20table%20like%20so%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EIf%20Not%20IsEmpty(oLr.Range(2)%20Then%20Set%20oLr%20%3D%20oLo.ListRows.Add(oLr.Index%2C%20TRUE)%0AoLr.Range(2).Value%20%3D%20Me.TextBox2.Value%0AoLr.Range(3).Value%20%3D%20Me.TextBox3.Value%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%2C%20that%20should%20give%20you%20some%20background%20on%20ListObjects%20and%20a%20bit%20on%20how%20to%20more%20efficiently%20address%20cells%20and%20ranges.%20Now%20let's%20go%20back%20to%20your%20problem.%20Let's%20begin%20with%20what%20we%20learned%20last%20time%20and%20build%20on%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I'm%20not%20entirely%20sure%20I%20understand%20the%20problem.%20My%20assumption%20is%20we%20want%20to%3C%2FP%3E%3CUL%3E%3CLI%3Eselect%20cells%20matching%20a%20material%20number%3C%2FLI%3E%3CLI%3Eif%20the%20selection's%20column%202%20is%20not%20blank%2C%20add%20a%20row%3C%2FLI%3E%3CLI%3EAdd%20data%20in%20columns%202%20%26amp%3B%203%20from%20the%20form%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20code%20below%20does%20this.%20I've%20added%20comments%20to%20help%20explain%20what%20each%20section%20does.%20Let%20me%20add%20a%20bit%20more%20explanation%20below%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%0A'%20%20%20Declare%20Variable%0A%20%20%20%20Dim%20oSelection%20As%20Range%0A%20%20%20%20Dim%20oArea%20As%20Range%0A%20%20%20%20Dim%20oRow%20As%20Range%0A%20%20%20%20Dim%20lRow%20As%20Long%0A%20%20%20%20Dim%20lLastRow%20As%20Long%0A%20%20%20%20Dim%20oLo%20As%20ListObject%0A%0A'%20%20%20Initialize%20Variables%0A%20%20%20%20lLastRow%20%3D%200%0A%20%20%20%20Set%20oLo%20%3D%20%5BTable1%5D.ListObject%0A%0A'%20%20%20Select%20rows%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%5BTable1%5D.AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3DMe.TextBox1.Value%0A%20%20%20%20Set%20oSelection%20%3D%20%5BTable1%5D.SpecialCells(xlCellTypeVisible)%0A%20%20%20%20%5BTable1%5D.AutoFilter%20Field%3A%3D1%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%0A'%20%20%20Process%20each%20area%20in%20selection%20and%20each%20row%20in%20each%20area%0A%20%20%20%20For%20Each%20oArea%20In%20oSelection.Areas%0A%20%20%20%20%20%20%20%20For%20Each%20oRow%20In%20oArea.Rows%0A%20%20%20%20%20%20%20%20'%20%20%20Calculate%20table%20row%20%23%0A%20%20%20%20%20%20%20%20%20%20%20%20lRow%20%3D%20oRow.Row%20-%20oLo.HeaderRowRange.Row%0A%20%20%20%20%20%20%20%20'%20%20%20Process%20only%20if%20we%20haven't%20already%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20lLastRow%20%26lt%3B%26gt%3B%20lRow%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20%20%20If%20empty%2C%20add%20a%20new%20row%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20IsEmpty(oRow.Cells(2))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lRow%20%3D%20oLo.ListRows.Add(lRow%20%2B%201%2C%20True).Index%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20oRow%20%3D%20oLo.ListRows(lRow).Range%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20oRow.Cells(1)%20%3D%20Me.TextBox1.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20%20%20Add%20data%20from%20form%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20oRow.Cells(2)%20%3D%20Me.TextBox2.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20oRow.Cells(3)%20%3D%20Me.TextBox3.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20DoEvents%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20%20%20Remember%20what%20we%20just%20processed%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lLastRow%20%3D%20lRow%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%0A%20%20%20%20Next%0A%0A%20%20%20%20UserForm1.Hide%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EoArea%20and%20oRow%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWhen%20we%20select%20filtered%20ranges%20there%20is%20a%20good%20chance%20the%20selection%20will%20not%20be%20contiguous.%20If%20that%20happens%20there%20will%20be%20multiple%20%3CSTRONG%3Eareas%3C%2FSTRONG%3E%20in%20the%20selection%20and%20each%20area%20could%20have%20multiple%20%3CSTRONG%3Erows%3C%2FSTRONG%3E.%20Whether%20the%20selection%20is%20contiguous%20or%20not%2C%20looping%20through%20selections%20by%20areas%20and%20then%20by%20rows%20is%20always%20the%20safe%20approach.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ElLastRow%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWhen%20we%20add%20rows%2C%20we%20change%20the%20shape%20of%20our%20selection%20so%20the%20next%20iteration%20through%20the%20loop%20will%20bring%20up%20the%20row%20we%20just%20added%20and%20if%20we%20process%20that%20row%20we%20will%20create%20a%20never%20ending%20loop%20that%20processes%20new%20row%20after%20new%20row%20until%20we%20reach%20Excel's%20row%20limit%20or%20use%20up%20all%20our%20memory.%20So%20we%20must%20remember%20which%20row%20we%20last%20processed%2C%20and%20if%20it%20is%20the%20same%20row%2C%20due%20to%20adding%20a%20row%2C%20skip%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a table with repeating entries in column 1. i have a button which adds data adjacent to each match of column 1.  this works thanks, @Craig Hatmaker you are amazing.

 

If there is already data in the adjacent columns and i want to add a new data entry for the same match then i would like the information displayed in a row beneath the row with data(leaving a blank space below the first column match followed by the data in column 2 and 3). this is as a pose to overwriting the data in the adjacent cells which is what happens currently.

 

I have made an attempt and a new row is entered but i cannot get the row to be entered at the right place. also, i made an error in syntax for calling the table to have a row added to it, any help is greatly appreciated

 

3 Replies

@Linesol 

Thanks for the kind words.

 

Before trying to solve the problem, let's learn about tables in VBA. In VBA they are ListObjects.

Dim oLo as ListObject

 

 

Getting (Setting) ListObjects

ListObjects belong to the worksheet's ListObjects collection. We can select the table from the worksheet's ListObjects collection in several ways. Here are 3.

Set oLo = ThisWorkbook.Sheets("Sheet1").ListObjects(1)

' or

Set oLo = Sheet1.ListObjects(1)

' or

Set oLo = Sheet1.ListObjects("Table1")

 

 

Note that because a ListObject is an object we must use Set.

 

We can also select a table by through a range's ListObject property. Any cell occupied by the table will return the ListObject like so (Assume A5 is in the table):

Set oLo = Sheet1.Range("A5").ListObject 

 

 

Now that we know ranges occupied by ListObjects can return the ListObject, we can leverage my preferred method: getting a ListObject by Table name. 

 

Note that table names MUST be unique within a workbook. Excel enforces this rule by tracking table names in its names collection, but the name does not refer to the ListObject, It refers to the table's range. Another odd note is the table name is not accessible from the workbook's names collection directly. We must use VBA's EVALUATE method to evaluate the name and return its range.

Set oLo = EVALUATE("Table1").ListObject 

 

 

So in the above, we evaluated the name "Table1" which returned the range that Table1 occupies and that range has a ListObject property that returns the ListObject in those cells.  Lastly, there is an abbreviation for EVALUATE which is [ ]. This is how I get ListObjects:

Set oLo = [Table1].ListObject

 

 

ListObject Properties

One of the wonderful things about tables is they expand. That means Excel must keep track of how many rows and columns a ListObject has.

Dim lRow as Long
Dim lCol as Long

lRow = oLo.ListRows.Count 
lCol = oLo.ListColumns.Count

 

 

Thus, we can loop through all table rows like so

For lRow = 1 to oLo.ListRows.Count 
...
Next 

 

 

And because the ListObject's ListRows is a collection of ListRow objects, we can loop through it using VBA's FOR/EACH:

Dim oLr as ListRow

For Each oLr in oLo.ListRows
...
Next

 

 

Each ListRow has an Index (row #) and a collection of cells. Thus we can set the cells in your table like so:

If Not IsEmpty(oLr.Range(2) Then Set oLr = oLo.ListRows.Add(oLr.Index, TRUE)
oLr.Range(2).Value = Me.TextBox2.Value
oLr.Range(3).Value = Me.TextBox3.Value

 

 

Okay, that should give you some background on ListObjects and a bit on how to more efficiently address cells and ranges. Now let's go back to your problem. Let's begin with what we learned last time and build on it.

 

Now I'm not entirely sure I understand the problem. My assumption is we want to

  • select cells matching a material number
  • if the selection's column 2 is not blank, add a row
  • Add data in columns 2 & 3 from the form

The code below does this. I've added comments to help explain what each section does. Let me add a bit more explanation below

Private Sub CommandButton1_Click()

'   Declare Variable
    Dim oSelection As Range
    Dim oArea As Range
    Dim oRow As Range
    Dim lRow As Long
    Dim lLastRow As Long
    Dim oLo As ListObject

'   Initialize Variables
    lLastRow = 0
    Set oLo = [Table1].ListObject

'   Select rows
    Application.ScreenUpdating = False
    [Table1].AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
    Set oSelection = [Table1].SpecialCells(xlCellTypeVisible)
    [Table1].AutoFilter Field:=1
    Application.ScreenUpdating = True

'   Process each area in selection and each row in each area
    For Each oArea In oSelection.Areas
        For Each oRow In oArea.Rows
        '   Calculate table row #
            lRow = oRow.Row - oLo.HeaderRowRange.Row
        '   Process only if we haven't already
            If lLastRow <> lRow Then
            '   If empty, add a new row
                If Not IsEmpty(oRow.Cells(2)) Then
                    lRow = oLo.ListRows.Add(lRow + 1, True).Index
                    Set oRow = oLo.ListRows(lRow).Range
                    oRow.Cells(1) = Me.TextBox1.Value
                End If
            '   Add data from form
                oRow.Cells(2) = Me.TextBox2.Value
                oRow.Cells(3) = Me.TextBox3.Value
                DoEvents
            '   Remember what we just processed
                lLastRow = lRow
            End If
        Next
    Next

    UserForm1.Hide

End Sub

 

oArea and oRow

When we select filtered ranges there is a good chance the selection will not be contiguous. If that happens there will be multiple areas in the selection and each area could have multiple rows. Whether the selection is contiguous or not, looping through selections by areas and then by rows is always the safe approach.

 

lLastRow

When we add rows, we change the shape of our selection so the next iteration through the loop will bring up the row we just added and if we process that row we will create a never ending loop that processes new row after new row until we reach Excel's row limit or use up all our memory. So we must remember which row we last processed, and if it is the same row, due to adding a row, skip it.

 

Hope that helps.

@Craig Hatmaker 

Hi Craig.

I hope you are keeping well.  That was quite a master class!

I must admit, I tend to go for quick and dirty solutions such as

Set oLo = Sheet1.ListObjects(1)

without necessarily knowing for sure which sheet is sheet1 or which table is the first in the collection (though it is pretty obvious if one guesses wrongly).  I don't think I ever understood why the ListObjects Collection should have a sheet as its Parent rather than the Workbook.

 

I think I will convert to 

Set oLo = [Table1].ListObject

which has the advantage of addressing the Table by name rather than index and is readable.

@Peter Bartholomew 

Hi Peter,

 

Thanks for the kind words.

 

Yes, I'm doing well. Had a great Thanksgiving with my wife, son, daughter, and new son-in-law. My oldest daughter couldn't make it :(. Had a deep fat fried turkey in peanut oil. Makes the bird quite juicy!

 

Hope you and your wonderful family are doing well too.

 

I like this for the reasoned you mentioned and that I don't care which worksheet it is on. I can move it to any worksheet and this will always find the table.

Set oLo = [Table1].ListObject