Macro question

Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
504
Office Version
  1. 365
Platform
  1. Windows
Good day. I am running Excel out of Office365 (updated) on Windows 10 Home. I have a worksheet that contains 4550 songs with 8 Columns in each Row. Row 1 contains Headers for the Columns. One of the Columns is named "Title" and another is "Year". I wrote a Macro to sort the entire worksheet by Year first and then by Title. It worked, but in looking at the Macro code, the last Row is identified as 4552 which is one more row than there is data. Then I wrote a second Macro to sort the entire worksheet back to Title only. That code showed the last Row as 4553. The sorts are fine and the extra blank Rows at the bottom are no problem. Is it normal for this to happen? If I end up writing any more Macros to sort the worksheet, will the last row keep adding a blank line? If I execute a Control End, the cursor ends up in Column H of the last Row (either 4552 or 4553). Thanks for any help.
Dan Wilson...
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
If you have code that defines your last row as the last row with data + 1 in a variable and you then define your range to sort using that variable, it likely will continue to add the extra row.. Just take the + 1 off the the code line where the last row variable is initialized so the code will not pick up the blank row. Or, if you do other stuff in the same macro, then use the last row variable - 1 in defining the sort range. It is just simple arithmetic to fix it.
 
Upvote 0
Solution
Good day JLGWhiz. Thank you for responding. The Macros were created using Record Macro and mouse selections. Apparently the End of the worksheet was created by the worksheet as there is no "data + 1" in the Macro. As the number of entries in the worksheet is continually being upgraded with new songs being added, I would have to edit the Macros every time I updated the worksheet. Therefore I will use the Sort option and manually sort the worksheet as needed.
Thank you, Dan Wilson...
 
Upvote 0
Thanks for the feedback. Is is possible that when recoding the macro the extra row was included in the sort range? If so, that would explain the additional blank row.
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top