So, I’m trying to get a good size spreadsheet (about 15 columns, hundred or so rows and growing) to be sortable by the columns. I’ve added filters to the column headers, which allows sorting forwards or backwards in any column.
The problem is, if a blank line is inserted anywhere in the middle of the data, which the users do from time to time, it only sorts up to the break. I need it to sort the whole spreadsheet, regardless of any breaks. I know I could do a sort macro with a command button for each column, but I really don’t want to take that much time to do 15 macros, plus if the spreadsheet grows beyond the sort boundaries, I’d have to update all 15 macros. Not what I want to do.
Select all the cells in your spreadsheet, including blank rows, then apply the filter. So for 15 columns and 100 rows, you’d select from cell A1 to cell O100. If you add rows or columns, you’ll have to remove the filter and re-apply it so the new records will show up.
If you use the Data, Sort command (in Excel 2003) then it will also include the blank rows. It is only maybe two extra mouse-clicks compared to using the Sort command in the the Autofilter.
Having wall-to-wall blanks in your raw dataset can cause problems. This filtering thing is one of them. Think of the reasons those blank rows are in there, and consider strongly removing them from the raw dataset.
Now, for a finished report, sure … go nuts with the blank rows. But for something you want to sort and filter … at least have one column that will always have data.