Sort on variable data Macro

[willi341]

Hello-

I'm working on creating one of my first macro's. I have a report that gets run daily. The number of rows of information will vary from day to day. I would like to be able sort all the data by one specific date column. I am able to select all the data with range selection. I am now just trying to do a sort with a header.

Any help is appreciated.

Bill

[MarvinP]

Hi Bill,

The way to learn how to do these things is to Record a Macro. Turn on the Macro Recording, do your sort, and turn it off again. Look at the VBA and start learning...

http://www.contextures.com/excel-macro-record-test.html

[willi341]

Marvin-

That's exactly what I've been doing. When I did the range selection then went to sort, it looks like it put the range in there. Since this will change day to day, I didn't think it would work.

Please Login or Register  to view this content.

[MarvinP]

Hi Bill

First could you edit your post, select the code and click on the "#" icon to put code markers on it, like I've done below.

Now you need to learn a little VBA

Please Login or Register  to view this content.

The above code creates a variable named LastRowN which is a number.
Then the next line goes to the last row in the worksheet (something like row 64,000 and in column "N")
It then does a Ctrl UP arrow and stops when it hits a non blank cell. Then it sends back the ROW that is in.

Now you can change your code using this variable by changing these two lines above..
Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

And for the

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

This is what us programmers do after we record a macro using the recorder. Then we change the last row to a variable and use it in the slightly modified code.

Another approach would be to use Dynamic Named Ranges in the code. For more about them read...
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.bettersolutions.com/excel...G820716330.htm

[willi341]

Hey Marvin-

Thanks.. I actually got it to work. After I got my range selected, I used

Please Login or Register  to view this content.

Then sorted on column 'E' and used

Please Login or Register  to view this content.

after that used

Please Login or Register  to view this content.

I'm still not exactly sure what I'm doing but it worked.

[MarvinP]

Good Job,

We are all wondering what most of our code is going to do. That is why they created the BreakPoint and F8 key to step through it a line at a time.

BTW - can you edit your second post above and put it in code tags. That way the moderator won't be mad at us and make your post a lot more readable.