Forum Discussion

Copper Contributor

Dec 01, 2020

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

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...

TEST.xlsm70 KB

Macros and VBA

PeterBartholomew1

Silver Contributor

Dec 01, 2020

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.

Craig Hatmaker

Iron Contributor

Dec 01, 2020

PeterBartholomew1

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