Sort A to Z with changing last row

[still learning]

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

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

 

[Peter_SSs]

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?

 

[still learning]

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

 

[Peter_SSs]

Try this

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

 

[still learning]

Hi Pete
Thank You
It works perfect
It sorts to the bottom row even when the bottom row changes.

Just a question so I can understand the macro and adapt it to other sheets

Why aren't they the same
Range("A3:L")....
and then ..Key1:=Range("A4")
I thing I understand >>>Key1:=Range("A4")<<< is where to start the sort but I don't understand why both ranges are different.
is Range("A3:L").... where the header is?

 

[Peter_SSs]

Thank You
It works perfect
It sorts to the bottom row even when the bottom row changes.

You're welcome. Thanks for the confirmation.

Why aren't they the same

They could have been the same, that's just how I happened to write it at the time.

 

[still learning]

Thanks again

mike

 

[Alex Blakenburg]

@still learning - FYI the cell used in the Key only tells the Sort which column to use. The cell row reference does not even need to be in the Sort Range.
If your sort range specifies a sheet which is not the active sheet then you also need to also specify the sheet in the Key reference.