Finding Last Row of Data in a Table

[snapfade]

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!

[notreallyIT]

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

[snapfade]

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.

[AB33]

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?

[snapfade]

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

[Leith Ross]

Hello snapfade,

This macro will clear the cells in column "A" that contain formula errors or are empty either because there is no data or as th result of a formula. It is setup for "Sheet1". Change this to match the sheet you are using.

Please Login or Register  to view this content.

[snapfade]

Hi Leith,

am getting a type mismatch on...

Please Login or Register  to view this content.

[snapfade]

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

[Leith Ross]

Hello Snapfade,

Try This out. LastRow has to be a Variant because match can return either a number or error.

Please Login or Register  to view this content.

[AB33]

Try

Please Login or Register  to view this content.

[snapfade]

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

Please Login or Register  to view this content.

[snapfade]

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

[AB33]

To be Decided!
I am stateless

[AB33]

Untested

Please Login or Register  to view this content.

[snapfade]

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

[AB33]

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.

[snapfade]

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?

[snapfade]

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

[AB33]

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.

[AB33]

Delete A-G

[snapfade]

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

[AB33]

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.

[snapfade]

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

[AB33]

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.