Macro question

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
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...
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Solution

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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...
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,432
Members
415,839
Latest member
Pollydooner

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
Top