Last row to end of sheet

[jomili]

I'm brain dead today I think, because I should know how to do this and can't think of it. I want to clear the contents of everything on my sheet that falls under the last populated cell in column A. My code for finding the last row in Column A is this:

Please Login or Register  to view this content.

I would use OFFSET to select the row under that, but I don't know how to select the range from that row to the bottom of the sheet to clear the contents.

If you have pity for the brain dead, please let me know how to do this.

[jomili]

I thought I had an idea, but this doesn't work:

Please Login or Register  to view this content.

I get a Compile error (Invalid Qualifier) on it.

[Mordred]

Hi Jomili, would the following code work for you?

Please Login or Register  to view this content.

[jomili]

Mordred, it didn't work, but that may be due to my explanation of what I need. I have data in about 40 columns. Column A defines my "pertinent" dataset. So, for all columns, anything that falls under the last row in column A needs to be cleared. As far as I can see, your macro isn't doing anything. It runs without error, but doesn't seem to do anything.

[jomili]

Mordred,

I changed the last line of your macro as follows:

Please Login or Register  to view this content.

I did it to see what it was determining as RngToClear. I got an error message saying "Select method of Range class failed".

[Mordred]

Hi jomili, I don't understand the error because when I run the code, even with .Select, it selects everything under the last row in Column A. I set up a sheet with data in each cell from A1:AZ8445 and then run the code and it selected everything under it. But, if I understand you now, if a column's row count pushes past the last row in column A, then clear the column?

[jomili]

No, not quite. If a column's row count pushes past the last row in column A, then we want to clear any part of the column that extends downwards of the last row in column A. See the attached example. In this one, columns N and O have data that extends below the last row in column A, so for this one everything from line 28 on down should be cleared.

[Mordred]

Try it like this then:

Please Login or Register  to view this content.

[jomili]

I'm sure it can be made better, but this one works:

Please Login or Register  to view this content.

[jomili]

Mordred,
I'm sorry; I didn't see your last post when I posted mine. Both routines seem to work just fine; I'm inclined to go with mine, since it's shorter; do you see a drawback to this?

[Mordred]

If it works, nope! I'm just glad you found a solution!

[jomili]

Me, too. Thanks so much for your help on this!

[Mordred]

No problem by me. I'll send you an invoice via carrier pigeon.