Problem Deleting Entire Rows

[CaptMac]

I have a sheet. I select all of it and "Deleted" it's contents a couple of different ways.

After I've "deleted" and run this code
With ActiveSheet
Last_Row = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With

"Last_Row" comes up with 3163 in it. How do I get it to say "0"?

The one solution I've found (but this isn't really practical) is to delete the rows, save the entire work sheet, close it, and read it back in. But I need to do these deletions under VBA code control.

Tnx, Mac

[Mumps1]

How did you delete the contents? Did you delete the rows or clear the cell contents?

[CaptMac]

I did it by deleting all the rows by selecting them first, not clearing.

[NoSparks]

don't think Excel will find rows less than 1.

put .UsedRange on a line right above your Last_Row =.... line

[Mumps1]

If you deleted the rows, that should have worked. You'll never get Last_Row to be equal to zero. Can you post a copy of your file?

[CaptMac]

So here is an example. Download "Book1.xlsm". Run the Macro "A" and read the messages.

The Macro begins with a delete. Then it searches for the LastRow and the LastCol in the LastRow it found.

I think the LastRow should be 3, but the computer doesn't think so.

Really could use some help here. Thanks,

Mac

[HaHoBe]

Hi, Mac,

itīs the normal behaviour of UsedRange to expand and notice that but not get the deletion correct (only after saving the workbook or resetting the last cell (see Reset All Lastcells in the Active Workbook on that) or maybe use

Please Login or Register  to view this content.

instead.

Ciao,
Holger

[Mumps1]

Hi Mac. When I ran macro A, it reported that the last row was 3 as you expected.

[HaHoBe]

Hi, Mumps,

I stated that after a save the UsedRange would be restored (as it happens automaticly when you close the workbook). Did you mind to add some contents to the range before running the macro? Iīm afraid you didnīt.

Ciao,
Holger

[Mumps1]

My apologies, Holger. You are perfectly correct.

[CaptMac]

Holger, You nailed it. Thanks for the Wallenberg tip.

Now, how do I mark this thread closed?

[HaHoBe]

Hi, Mac,

from the FAQ:

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Old Method
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Ciao,
Holger