Macro Adjust Based on Dataset?
 

Good morning all.

BACKGROUND:
I have a macro recorded to format data in my spreadsheet to a report when
printed (adjust column width, page setup, bold text, sort data excluding
headers etc.). In the portion of that macro provided below (for sorting the
data), the range to sort looks up the range A:1 to I1087 as when I originally
recorded the macro, that was the size of my 'test' dataset. The spreadsheet
will be used to accomodate many different datasets; smaller or larger numbers
of records to sort. The result as is will be that if I have a dataset that
goes through row K1087 for example, after the macro is run, the records
through I1087 are sorted leaving the rest unsorted.

QUESTION:
Is there a way, through coding or otherwise, to write something in the
recorded macro that will adjust the sort range to whatever length of dataset
is being worked with without having to manually change the macro everytime I
run the macro for the accompanying dataset?


Sample of Macro Sorting Code form my spreadsheet:

Range("A1").Select
Range("A1:I1087").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
Range("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Thanks in advance for your time.

Macro Adjust Based on Dataset?
 

You can just set the value to a number larger than your largest anticipated
record set such as say 20000. That being said ther eis still the risk of
getting a record set alrger than you ever imagined. That happens to me a lot.
So I would tend to use code such as this...

dim rngToSort as range

set rngToSort = range(range("I1"), cells(rows.count, "A").end(xlup))
rngToSort.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:= Range("B2"), Order2:=xlAscending,
Header:=xlYes


--
HTH...

Jim Thomlinson


"Jcraig713" wrote:

Good morning all.

BACKGROUND:
I have a macro recorded to format data in my spreadsheet to a report when
printed (adjust column width, page setup, bold text, sort data excluding
headers etc.). In the portion of that macro provided below (for sorting the
data), the range to sort looks up the range A:1 to I1087 as when I originally
recorded the macro, that was the size of my 'test' dataset. The spreadsheet
will be used to accomodate many different datasets; smaller or larger numbers
of records to sort. The result as is will be that if I have a dataset that
goes through row K1087 for example, after the macro is run, the records
through I1087 are sorted leaving the rest unsorted.

QUESTION:
Is there a way, through coding or otherwise, to write something in the
recorded macro that will adjust the sort range to whatever length of dataset
is being worked with without having to manually change the macro everytime I
run the macro for the accompanying dataset?


Sample of Macro Sorting Code form my spreadsheet:

Range("A1").Select
Range("A1:I1087").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
Range("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Thanks in advance for your time.