Macro Adjust Based on Dataset?

J

Jcraig713

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.
 
J

Jim Thomlinson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top