Sort A to Z with changing last row

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
845
Office Version
  1. 365
Platform
  1. Windows
Hi
I’m trying to sort from data in A to L
The last row changes with new data
I have A3 frozen as I use it as a header
Data starts at A4
A:A has dates in it
I need the oldest date to start in A4 (A to Z)
As I add data, sometimes I can’t add in order
This works great but it doesn’t go to the last row that has data
I tried using the macro recorder but it also won’t go the last row. It only sorts up to when it was recorded.
As of today the last row is A39:L39, but I add data every few days
VBA Code:
Sub atoz()    
        .Range("A4", .Range("A" & Rows.Count).End(xlUp)).Resize(, 7).Sort _
            Key1:=.Range("A11"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal    
End Sub


Mike
 
I’m trying to sort from data in A to L
Then why do you only Resize(, 7) in the code? That will only sort A to G

Also just wondering why you have Key1 as A11 when you data starts in row 4?

This works great but it doesn’t go to the last row that has data
Can any of the sorted columns have data lower than the last data in column A?

Is your data ever filtered when you run the code?

You have only shown us part of the code. If the issue is not easily resolved you may need to show us the whole code.

Is there any other code anywhere in the workbook?
 
Upvote 0
Hi Pete

Opps, I copied the wrong macro. That was for a different workbook. I'll get the right one, but it'll take a few days.
I'm going to try and make a sample worksheet and post it, again, it'll take a few days
To answer your questions.
There is no filtering
There is a header. A3 is frozen. Which is the header

"Can any of the sorted columns have data lower than the last data in column A?"
Yes, That is where I enter the data before I want to sort.

As an example:
I enter a date in the last row of A the is empty.
I then fill in the some or all of columns B:L with data
Then I drop down one row and do it again
sometimes I get the dates in the wrong order
When I'm done, I click on an icon on the ribbon that sorts the rows (A to Z)

I would like the macro to recognize the last row of data to sort
What it does now is only sorts up to the last row that was there when I made the macro

I used the recorder. I'm not smart enough to start from scratch !!!!


mike
 
Upvote 0
Try this

VBA Code:
Sub Test()
  Range("A3:L" & Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row) _
    .Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes
End Sub
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top