I have a named block, used to sort a list of data. But I names the block one row higher to include the headers, so that inserts at the top row of the data would not fall out of the named range.
Then I want to sort the data, but not include the header row.
How?
What I want to say in the sort macro is something like:
cell_range( top_row(Data)-1, Bottom_Row(Data) )
Or, to have two names, with one defined in terms of the other:
Data_sort = Data_big - Toprow(Data_big)
Last edited by guthrie; 10-23-2008 at 08:30 AM. Reason: solved
Hello guthrie,
Welcome to the Forum!
Here is a code example in VBA of resizing a range to remove the header row - row 1.
Sincerely,
Sub RemoveHeaderRow() Dim Rng As Range 'Set Rng equal to the block range A1:I10 Set Rng = Range("A1:I10") 'Reset Rng equal to the block range minus row A1:I1 Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1) 'Sort the Range Rng.Sort End Sub
Leith Ross
Hi,
aa
In the sort dialog box you have an option to specify whether your data has a header row or not. If you specify a header row then the first row will be excluded from the sort.
If you are using a macro then you have to include that option as one of the sort parameters. Your VBA code will be something like this:
Regards.Range("A10:B14").Select Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
Welcome to: http://www.exceldigest.com/myblog/
"Excel help for the rest of us"
Many thanks to you both - good solutions!
Greg
Problem solved ? Please change the prefix of your original post accordingly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks