Finding Last Row of Data in a Table

My table has 182 rows and formulas in many columns but Column A is the key. It has data down to only row 18. I want to delete all rows of the table beneath the last row where column A has data. That is, I want to delete table rows 19 through 182.

When I use the...

Please Login or Register  to view this content.

it yields 182, not 18. How can I modify this code to get the right answer?

Thanks!

lastrow = thisworkbook.sheets(1).cells(rows.count,"a").end(xlup).row

Thanks for the quick reply, notreallyIT, but this code yields 182 as does my original code.

I think the challenge here is that I am using the newish Tables feature (really quite awesome). Tables are ListObjects and I suspect special coding may be needed.

Please Login or Register  to view this content.

will find the last row in table.
How is your goggling going on? Any magic short cut yet?

Hi again AB33.

Your code yields 182, just like the other examples. There are 182 rows in the table and all columns other than A have formulas. But Column A only has data down to row 18 so I want to delete rows below that.

I have quit tilting at windmills for the time being. ;-(

Hi Leith,

am getting a type mismatch on...

Please Login or Register  to view this content.

Leith, I should have asked what that line does. Also, I think I may want to delete the rows rather than clear contents.

Try

Please Login or Register  to view this content.

Leith, now getting the type mismatch on the next line...

Please Login or Register  to view this content.

AB33...progress. Selected the last row of data. Now, how do we delete the rest of the records in the table?

BTW, where is TBD? I asked Carmen San Diego and she was clueless. ;-)

To be Decided!
I am stateless

Untested

Please Login or Register  to view this content.

Ouch! that deleted column A. And I just learned that there is no undo. no problem...have a version from yesterday.

snapfade,
This is a learning process. As a good practice, you should always save your data before you run a code.
I was not sure sure if it is going to work.

Not a problem, AB33. This isn't my first rodeo. When working on a project like this I save a new copy at the start of every day and sometimes throughout the day. THAT I learned long ago and far far away in a place near TBA.

Any suggestions on fixing that code?

Attached is a sample table. Col A is rows 2-13. Rows to be deleted are 14-29

Well, I now have tested it and seems to copy the desired range. I have chosen copy rather than delete.
Try it.

Please Login or Register  to view this content.

Delete A-G

Wonderful! Works perfectly. Thanks for all your help, AB33. Hope you find a state soon. tom

Two more clarification.

1. To be Announced( Not decided)
2.When I said save, I was not referring to the usual save. This is how I normally work with macros: Copy the code in the module first then before I run it , I save my file. If the code is wrong, I can go back to the pre-run stage. This preserves my original data and the code is still on the module.
You probably did not get the message. I asked you how is your search for ideal VBA course going on.

1. good luck
2. understood
3. I did reply -- that I am done tilting at windmills for now. Still interested but less enthusiastic about finding a course. Hoping to find a good reference. It just drives me nuts that there are so many ways to get a line of code wrong...and so few ways to get it right. Clearly, it is trial and error because if it made more sense it would be called trial and success. I am thankful for this forum; it has been enormously helpful in my work and by the time i have 7000 posts I too may have a clue. ;-)

Thanks again!
Merry Christmas
tom

Given your back ground in programming, I am sure you do not need to reach the 7 figure.
Once you start kicking-in through trial and error, all your previous knowledge will come in handy to help trim the 7 figure.