Hi Apple Ling;
Techinically, this new problem should be in a new thread, because it has nothing to do with the original question. So don't be surprised if a moderator says something about it.
CurrentRegion doesn't select all cells in the sheet. It only selects the cell that are surrounded by spaces. In your sample there are blank rows between row 1 and row 17, so CurrentRegion doesn't get them all.
UsedRange is what you should use to get all used cells in the sheet.
One warning about UsedRange. It doesn't always start in A1. If Row 1 is blank, and Column A is blank, then UsedRange's top left corner will be B2.
If you always want to have A1 as the top left corner then use this
Another note:
I recommend not using
Sheets(1) is not always the first sheet.
A safer way:
One Final Note, for more user friendly code:
Your section:
would be better like this:
That way, you don't have to delete the sheet every time you run the macro.[/SIZE]
Bookmarks