Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
I am trying to select a variable range, beginning in column A and ending in
the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Hi,
Try this: Sub Macro1() ' Macro1 Macro ' Macro recorded 1/24/2009 Range("A1").Select Cells.Find(What:="FREQ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate TopCell = ActiveCell.Address Selection.CurrentRegion.Select Selection.Sort Key1:=Range(TopCell), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(TopCell).Select End Sub David "WLMPilot" wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
You don't have to work with selections in your code for most things.
If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
I think using CurrentRegion in your code will cause a problem in the sort if
there are any blank rows or blank columns in the data area. -- Rick (MVP - Excel) "David" wrote in message ... Hi, Try this: Sub Macro1() ' Macro1 Macro ' Macro recorded 1/24/2009 Range("A1").Select Cells.Find(What:="FREQ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate TopCell = ActiveCell.Address Selection.CurrentRegion.Select Selection.Sort Key1:=Range(TopCell), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(TopCell).Select End Sub David "WLMPilot" wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Here is a macro which uses your Sort code, but which automatically
determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Of course, a better name for my macro might be SortDataBelowFREQ (or
something else) rather than the original macro name I posted (which I created in response to the code I developed for simply selecting the appropriate range in order to answer the first part of the OP's question). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Won't those .finds (with xlprevious) depend on where the activecell is?
And I bet you want header to be xlno. And if I know the layout of my data, I see nothing wrong with using the ..end(xlup) or .end(xltoleft) stuff. Rick Rothstein wrote: Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
I screwed up my With..End With blocking. This is the macro I should have
posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
And, as Dave has pointed out, the Sort routine should use xlNo for the
header argument in the Sort statement. Here is the corrected code... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlNo, MatchCase:=False End With End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Wow! Thanks for all the responses. I think I got more coding than is
necessary, but I am not an expert at it by any means. Let me ask it different way and see what happens. The only unknown point of the range selection needed is the location of the upper left most cell, which is in column A, and the first cell below the header Freq. I think the bottom and right side intersection can be determined with xlup and xlleft (or whatever the correct function is). Below is part of a macro that selects a range then sorts on another worksheet. I show you this to let you know basically what I am looking for. I think that once the upper left corner is located, then a RANGE can be set up similar to what is shown below: Range("A" & begrng & ":Q" & endrng).Select Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending, Key2:=Range("A" & begrng) _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I hope this helps to simplify what I am wanting. Again, thanks for your suggestions and if what I think is wrong, please let me know and I will go back and look at the suggestions given. Thanks, Les "Rick Rothstein" wrote: I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
See inline comments...
Won't those .finds (with xlprevious) depend on where the activecell is? No, I don't think so. Since I have not provided an After argument, the search starts from the cell after the cell in the upper left corner of the range (namely, "after" A1)... the range is all cells and, since xlPrevious is used, the cell "after" A1 is the one in the bottom right corner of the worksheet; hence, all searches take place from the bottom upward or right side leftward. And I bet you want header to be xlno. Yep! I didn't pay enough attention to the your argument list when I included it with my own code... since I am omitting the headers in my selection, the "header" argument needs to be xlNo. And if I know the layout of my data, I see nothing wrong with using the .end(xlup) or .end(xltoleft) stuff. The problem with that, as I see it, is that those methods need to specify the row/column with data to the end. There is no guarantee that the last header column will have data in it (could be a Comment column with no entries in it), although that probably wouldn't screw up the sort. Also, there is no guarantee that the last filled cell in Column A (or any other column you might choose for that matter) will always contain the maximum in-use row number. The code I suggested avoids these issues. -- Rick (MVP - Excel) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Before I could comfortably say xlUp and xlToLeft are alright to use, you
need to tell us if there is a row and a column in your data set that **always** contains the maximum data element for that row or column. The Column one can more than likely use the header row, but do you have a specific column whose last filled in cell will **always** be in the maximum used row? Also, as others have to you, there is no need to select the range in order to sort it. As a matter of fact, it is rarely necessary to ever select a range in order to operate on it. Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "WLMPilot" wrote in message ... Wow! Thanks for all the responses. I think I got more coding than is necessary, but I am not an expert at it by any means. Let me ask it different way and see what happens. The only unknown point of the range selection needed is the location of the upper left most cell, which is in column A, and the first cell below the header Freq. I think the bottom and right side intersection can be determined with xlup and xlleft (or whatever the correct function is). Below is part of a macro that selects a range then sorts on another worksheet. I show you this to let you know basically what I am looking for. I think that once the upper left corner is located, then a RANGE can be set up similar to what is shown below: Range("A" & begrng & ":Q" & endrng).Select Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending, Key2:=Range("A" & begrng) _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I hope this helps to simplify what I am wanting. Again, thanks for your suggestions and if what I think is wrong, please let me know and I will go back and look at the suggestions given. Thanks, Les "Rick Rothstein" wrote: I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Now will find "Freq" and sort below that using cell a & row below "Freq"
IF??, instead of finding the last column in row 1 you want the last column in the Freq row, change the lc= to Cells(FR, Columns.Count).End(xlToLeft).Column Sub sortareabelowVariable() Dim fr, lc, lr As Long fr = Columns(1).Find("Freq").Row lc = Cells(1, Columns.Count).End(xlToLeft).Column lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row 'MsgBox lc 'MsgBox lr Range(Cells(fr, 1), Cells(lr, lc)).Sort _ Key1:=Cells(fr + 1, "a"), Order1:=xlAscending, Header:=xlYes, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "WLMPilot" wrote in message ... Wow! Thanks for all the responses. I think I got more coding than is necessary, but I am not an expert at it by any means. Let me ask it different way and see what happens. The only unknown point of the range selection needed is the location of the upper left most cell, which is in column A, and the first cell below the header Freq. I think the bottom and right side intersection can be determined with xlup and xlleft (or whatever the correct function is). Below is part of a macro that selects a range then sorts on another worksheet. I show you this to let you know basically what I am looking for. I think that once the upper left corner is located, then a RANGE can be set up similar to what is shown below: Range("A" & begrng & ":Q" & endrng).Select Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending, Key2:=Range("A" & begrng) _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I hope this helps to simplify what I am wanting. Again, thanks for your suggestions and if what I think is wrong, please let me know and I will go back and look at the suggestions given. Thanks, Les "Rick Rothstein" wrote: I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
This post describes the code I suggested.
You may want to take a look at it again. WLMPilot wrote: Wow! Thanks for all the responses. I think I got more coding than is necessary, but I am not an expert at it by any means. Let me ask it different way and see what happens. The only unknown point of the range selection needed is the location of the upper left most cell, which is in column A, and the first cell below the header Freq. I think the bottom and right side intersection can be determined with xlup and xlleft (or whatever the correct function is). Below is part of a macro that selects a range then sorts on another worksheet. I show you this to let you know basically what I am looking for. I think that once the upper left corner is located, then a RANGE can be set up similar to what is shown below: Range("A" & begrng & ":Q" & endrng).Select Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending, Key2:=Range("A" & begrng) _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I hope this helps to simplify what I am wanting. Again, thanks for your suggestions and if what I think is wrong, please let me know and I will go back and look at the suggestions given. Thanks, Les "Rick Rothstein" wrote: I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Thank you for your suggestions and advice.
Les "Rick Rothstein" wrote: And, as Dave has pointed out, the Sort routine should use xlNo for the header argument in the Sort statement. Here is the corrected code... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlNo, MatchCase:=False End With End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Thank you for your suggestions.
Les "Don Guillett" wrote: Now will find "Freq" and sort below that using cell a & row below "Freq" IF??, instead of finding the last column in row 1 you want the last column in the Freq row, change the lc= to Cells(FR, Columns.Count).End(xlToLeft).Column Sub sortareabelowVariable() Dim fr, lc, lr As Long fr = Columns(1).Find("Freq").Row lc = Cells(1, Columns.Count).End(xlToLeft).Column lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row 'MsgBox lc 'MsgBox lr Range(Cells(fr, 1), Cells(lr, lc)).Sort _ Key1:=Cells(fr + 1, "a"), Order1:=xlAscending, Header:=xlYes, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "WLMPilot" wrote in message ... Wow! Thanks for all the responses. I think I got more coding than is necessary, but I am not an expert at it by any means. Let me ask it different way and see what happens. The only unknown point of the range selection needed is the location of the upper left most cell, which is in column A, and the first cell below the header Freq. I think the bottom and right side intersection can be determined with xlup and xlleft (or whatever the correct function is). Below is part of a macro that selects a range then sorts on another worksheet. I show you this to let you know basically what I am looking for. I think that once the upper left corner is located, then a RANGE can be set up similar to what is shown below: Range("A" & begrng & ":Q" & endrng).Select Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending, Key2:=Range("A" & begrng) _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I hope this helps to simplify what I am wanting. Again, thanks for your suggestions and if what I think is wrong, please let me know and I will go back and look at the suggestions given. Thanks, Les "Rick Rothstein" wrote: I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection
Thank you for your suggestions.
Les "Dave Peterson" wrote: This post describes the code I suggested. You may want to take a look at it again. WLMPilot wrote: Wow! Thanks for all the responses. I think I got more coding than is necessary, but I am not an expert at it by any means. Let me ask it different way and see what happens. The only unknown point of the range selection needed is the location of the upper left most cell, which is in column A, and the first cell below the header Freq. I think the bottom and right side intersection can be determined with xlup and xlleft (or whatever the correct function is). Below is part of a macro that selects a range then sorts on another worksheet. I show you this to let you know basically what I am looking for. I think that once the upper left corner is located, then a RANGE can be set up similar to what is shown below: Range("A" & begrng & ":Q" & endrng).Select Selection.Sort Key1:=Range("E" & begrng), Order1:=xlAscending, Key2:=Range("A" & begrng) _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I hope this helps to simplify what I am wanting. Again, thanks for your suggestions and if what I think is wrong, please let me know and I will go back and look at the suggestions given. Thanks, Les "Rick Rothstein" wrote: I screwed up my With..End With blocking. This is the macro I should have posted... Sub SortDataBelowFREQ() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub I guess I should point out that I used ActiveSheet, but if you want this code to apply to a specific sheet, just change the ActiveSheet reference in the first With statement to Worksheets("SheetX") where you would replace SheetX with the worksheet's actual name. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a macro which uses your Sort code, but which automatically determines the last used row and last used column, *within* the actual data area (those rows below the FREQ header row), to apply it to... Sub SelectDataRows() Dim FREQrow As Long Dim LastRow As Long Dim LastColumn As Long Dim DataRows As Range With ActiveSheet FREQrow = .Cells.Find(What:="FREQ", After:=Range("A1"), _ LookAt:=xlWhole, MatchCase:=True).Row LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row Set DataRows = .Range(FREQrow & ":" & LastRow) LastColumn = DataRows.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With With .Range(.Cells(FREQrow + 1, "A"), .Cells(LastRow, LastColumn)) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End Sub -- Rick (MVP - Excel) "Dave Peterson" wrote in message ... You don't have to work with selections in your code for most things. If you do, I think you'll find the code harder to understand and maintain. You didn't say how you wanted the sort done (or did I miss that???). Option Explicit Sub testme() Dim TopLeftCorner As Range Dim BotRightCorner As Range Dim LastRow As Long Dim LastCol As Long Dim StringToSearchFor As String Dim wks As Worksheet Set wks = ActiveSheet 'worksheets("sheetnamehere") StringToSearchFor = "Freq" With wks With .Range("A1").EntireColumn Set TopLeftCorner = .Cells.Find(What:=StringToSearchFor, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If TopLeftCorner Is Nothing Then MsgBox StringToSearchFor & " wasn't found!" Exit Sub End If 'using column A to determined the last row to sort LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'using the headers in the row with Freq to determine 'the last column to sort LastCol _ = .Cells(TopLeftCorner.Row, .Columns.Count).End(xlToLeft).Column Set BotRightCorner = .Cells(LastRow, LastCol) With .Range(TopLeftCorner, BotRightCorner) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(LastCol), order3:=xlAscending, _ header:=xlYes, MatchCase:=False End With End With End Sub The sort statement includes the row with Freq, but header:=xlyes means that it won't be sorted as part of the data. WLMPilot wrote: I am trying to select a variable range, beginning in column A and ending in the furtherest column to the right being used AND last row used. There are three factors to remember: 1) The actual beginning point (upper leftmost cell) may change as I insert/delete rows above the data to be sorted. The first cell in column A for the sort range is located below the header FREQ. 2) The number of rows down will increase as data is added. 3) The number of columns used may increase if I determine there are other items I need to calculate/input. Currently, I am using columns A-R. The column headers are on row 8, which may increase/decrease based on item #1 above. One other quick question: I believe I have the sort code I need. My question is this: After the range is selected, I believe the first part of the sort code is "Selection:Sort" (without quotes). Is this correct? Thanks for your help! Les -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Narrow Range based on Selection in Another Range | Excel Discussion (Misc queries) | |||
Range(Selection, Selection.End(xlToRight)).Select | Excel Programming | |||
Identifying a selection of a selection of a range | Excel Worksheet Functions | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming | |||
Creating range name for a range selection | Excel Programming |