How to Sort Rows by dynamically determining first and last row

I have a worksheet that has a few rows of header information. I'd like to give those rows a name of "DashboardHDR".
I'd like to locate the FIRST ROW after that named range of rows.
Then I'd like to locate the final row by looking in column B for BLANKS and then the previous row is the LAST ROW.
Then I'd like to sort from FIRST ROW to LAST ROW, ascending, on the value in column F.

Bonus Question: Column F header value is "Work Type". The last row in "DashboardHDR" contains the header text. Would it be possible to designate the sort column by locating the column associated with header text = "Work Type"?

I have to give full disclosure ... I am not an Excel programmer. I am a programmer (C, assembler/mainframe, pltpf/sabrtalk, VBA). I've done some VBA coding for Access, but fully self-taught. So, I have a little knowledge and know what I want to do ... a dangerous combination.

My goal: create a macro and a button on the worksheet to sort the detail rows on the sheet by a particular column, regardless of how many rows of detail data there is.

Thanks, in advance, for any assistance.

Hi,

It should be fairly trivial to get a macro to identify two Long (Integer) variables that identify the first and last row numbers to be included in the sort and a third variable to identify the Work Type column. Then armed with the 3 variables a simple Sort line of code will achieve what you want.

For a more specific response we'll need to see the workbook.

Ok. Great. Here (attached) is my spreadsheet. As you will see the 'Dashboard' and 'Additional Info' tabs already
have buttons with macros assigned to do various sorts. The problem is that the row ranges are hard coded in each macro.
My goal is to make the sort actions dynamic; that is the macro will determine how many rows based on the criteria I initially stated:

1. First Row is the row immediately following the header rows (Range Name: "DashboardHDR")
2. Last Row is the row immediately preceding the row that has BLANKS in the cell with Column Header Text "Tab Name".
(Column B)

Once I see it done for the Dashboard I think I'll be able to figure it out for tab 'Additional Info'. Perhaps I'll be
able to make the macros usable across worksheets (that is use the same sort macros for 'Dashboard' tab and
'Additional Info' tab.

Well .. good luck and THANK YOU!!!

Here's one approach

Point the button on the Dashboard to the SortDashboard procedure. This passes the VB name of the sheet to the SortSheets Procedure. For other sheets create other Procedures similar to the SortDashboard procedure.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Just an observation and unrelated to your request but I note that you are using merged cells. Many of us avoid these like the plague since they can cause problems with other aspects of Excel and VBA code and with more complex workbooks one day you'll find these trip you up.

Richard,
Thank you for the help. I am almost there. I tweaked the SortSheets routine, that you provided, by adding another input argument, enabling it to be called for a variety of sorts (sorting on different columns). And since there is no row containing the text "Projects Removed" on the "Additional Info" tab I am attempting to search for the first row with blanks in the cells. This is not working yet. I'm getting the following error "Runtime Error '13', Type Mismatch" on the following statement:

Please Login or Register  to view this content.

Do you have any ideas about this error? Here is the entire routine and how it will be called (the calling routines).

Please Login or Register  to view this content.

Thanks.
Regards,
Sue

Hello Sue,

Just dashing out for the evening but you appear to be trying to find a space character " " as opposed to presumably a completely blank cell.
Try instead a Null i.e. ""

If you're still struggling upload the workbook and I'll take a look later.

Hi, Richard.

Well, I'm really stuck on trying to find the first blank line. Here's the routine with the statement that is failing in red:
I am trying to locate the last line of the list (rows) of projects. So, I would like to search for the first 'blank' row.
Take a look at the 'Dashboard' tab in the workbook attached. You will see that there are two "tables" of information and I would like to sort the detail lines of only the first 'table'.

If you have a suggestion, please let me know.

Please Login or Register  to view this content.

Hi,

In that case and assuming there is always a contiguous series of cell in column A below the lFirstRow variable follwed by a blank cell the I'd be inclined to use

Formula:

Please Login or Register  to view this content.

instead of the .Find syntax

Thank you for the assistance, but it's not exactly what I need.

The 'Dashboard' tab (Sheet1) has a "bunch of rows" populated then it has a blank row and then it has another 'bunch of rows' populated.
I'd like to row number of the single blank row. Then subtract 1 to determine the LastRow for the sort action.
That row number should be the value of lLastRow.

Just for testing, I've hard coded a value for the LastRow and continued to test and the attempt to determine the column to be sorted on is failing with the following error:

"Run-time error '91':
Object variable or With block variable not set"

Here's the routine with the line that is failing in red:

Please Login or Register  to view this content.

Any help would be appreciated.

Thanks, much.
Regards,
Sue

The lSortColumn =.... line works for me. What's the value of sColTxt when it Debugs?

I'd still be inclined to use the following to find the last row (given the caveats I mentioned earlier) rather than hard coding the variable.
lLastRow = sh.Cells(lFirstRow, "A").End(xlDown).Row

Hi. sColTxt has a value of "Work Type".

While in Debug ... hovering over the variable definition in the sub( ... ) line shows

sColTxt = "Work Type"

This is what I would expect, when clicking on the 'Sort by Work Type' button on the "Additional Info" tab. (note: I can't cut and paste from the actual running of the workbook because I'm running it on my work laptop and writing to you on my home PC).


Stepping thru the instructions/statements while in Debug, when I get to the attempt to populate lSortColumn (when using the hardcoded instructions to set lLastRow) the instruction is:

Please Login or Register  to view this content.

I get the following error:

Run-time error '91':
Object variable or With block variable not set

When I use the following to attempt to populate lLastRow:

Please Login or Register  to view this content.

I get the following error:

Run-time error '1004':
Application-defined or object-defined error

I'll attach a copy of the workbook with all of the variations for populating these variables (commented out lines that I keep commenting or uncommenting for testing/debugging).
You will find the code in Module2.

To say that this is very frustrating is putting it mildly. (I love programming. I love programming. I love programming. ... I say red-faced).

Thanks for your assistance.
Regards,
Sue

Hi,

It would appear that the .End(xlDown) syntax won't work after a .Cells instruction. Try instead

Formula:

Please Login or Register  to view this content.

Hi,

Would you upload the latest workbook you're using so that I can see the error in context.

HOLY COW!!! I got it to work. Admittedly, I did use an inelegant approach, but at this point I must move on to actually attending to my other work responsibilities. I'll embed some of the code, below. I won't mark this as solved quite yet so as to get the opinions of some of you experts. After 1 or 2 comments, then I'll mark this as solved.

Here are some of the problems that I ran into along the way:

1. Wanted to have a sort routine that could be called from different worksheets
2. Wanted to dynamically determine the first row of the area to be sorted
3. Wanted to dynamically determine the last row of the area to be sorted
4. Wanted to let the caller of the sort routine designate which column to sort on and in which direction to sort (ascending or descending)

Each of these "wants" gave me problems, but with the assistance of a couple of you experts, I was able to find a solution. It's not the most elegant of solutions, but for my needs, it works. Here it is. Please respond with constructive comments. I, always, welcome new information.

BTW ... the following routines/macros are assigned to buttons on the worksheets:
SortDashboard_byName
SortDashboard_byE0
SortDashboard_byWorkType
SortAdditionalInfo_byName
SortAdditionalInfo_byWorkType
SortAdditionalInfo_byRequirements

Please Login or Register  to view this content.

Here is the inelegent code to determine column letter from the column index number. Since I know for sure that the number of columns will definitely not go beyond column Z I chose this method.

Please Login or Register  to view this content.

Here is an example of calling the 'SortProjects' routine:

Please Login or Register  to view this content.

Thanks, again.
Regards,
Sue

Hi Sue,

I don't follow the logic with the call to the GetColumnLetter Function in the line

Please Login or Register  to view this content.

The lColumns value you pass will always be the same. i.e. the number of columns in the currentregion which will never change. If there are 26 columns in the current region then you'll always return "Z"

Is there any reason why you can't use the same basis instruction as you use for the lColumns Variable. i.e.

Formula:

Please Login or Register  to view this content.