Change Recorded Range To Used Range.

[Dazzawm]

I have this section of code that I recorded, how do I change it to the used range so it can be used on different files that have different amount of rows and columns please?

Cells.Select
    Sheets("Sheet1").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("C2:C611582"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range( _
        "A2:A611582"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ611582")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Joe4]

You can capture the Used Range in a range object variable like this:

'   Capture used range in a range object variable
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange

Then you can replace range references in your code with this variables, i.e.
this:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ611582")

can be replaced with:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng

 

[Dazzawm]

You can capture the Used Range in a range object variable like this:

'   Capture used range in a range object variable
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange

Then you can replace range references in your code with this variables, i.e.
this:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ611582")

can be replaced with:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng

Thanks but what about the top bit of code where there are other ranges?

 

[Joe4]

Thanks but what about the top bit of code where there are other ranges?

They are not referencing the complete Used Range, but rather specific columns. So that would not work for those ones.

Perhaps I would suggest a different method, if that is what you want to do. Rather than find the Used Range, how about finding the last row, i.e.

Dim lr as Long
lr = Range("A1").SpecialCells(xlLastCell).Row

Then, just substitute this in for your various ranges, i.e.
this:

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("C2:C611582"), _

becomes this:

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("C2:C" & lr), _

and this:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ611582")

becomes this:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ" & lr)

etc.

 

[Dazzawm]

They are not referencing the complete Used Range, but rather specific columns. So that would not work for those ones.

Perhaps I would suggest a different method, if that is what you want to do. Rather than find the Used Range, how about finding the last row, i.e.

Dim lr as Long
lr = Range("A1").SpecialCells(xlLastCell).Row

Then, just substitute this in for your various ranges, i.e.
this:

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("C2:C611582"), _

becomes this:

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("C2:C" & lr), _

and this:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ611582")

becomes this:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AZ" & lr)

etc.

But AZ may not be the last column in other files?

 

[Joe4]

Well, you could find the last used cell like this:

'   Find last cell on sheet
    Dim lastCell As Range
    Set lastCell = Range("A1").SpecialCells(xlLastCell)

Then, where you are trying to reference the ENTIRE Used Range, you could do it like this:
replace this:

.SetRange Range("A1:AZ611582")

with this:

.SetRange Range("A1:" & lastCell.Address)

And where you want to update the last row on specific columns, you could replace references like this:

Range("C2:C611582")

with this:

Range("C2:C" & lastCell.Row)

 

[Dazzawm]

Well, you could find the last used cell like this:

'   Find last cell on sheet
    Dim lastCell As Range
    Set lastCell = Range("A1").SpecialCells(xlLastCell)

Then, where you are trying to reference the ENTIRE Used Range, you could do it like this:
replace this:

.SetRange Range("A1:AZ611582")

with this:

.SetRange Range("A1:" & lastCell.Address)

And where you want to update the last row on specific columns, you could replace references like this:

Range("C2:C611582")

with this:

Range("C2:C" & lastCell.Row)

I am getting well confused now! Could you write the code as I need it please?

 

[Joe4]

Please make an effort to try to understand it. I am not a big fan of just spoonfeeding answers and have people use it, having no idea how it works. I like to help people learn, not just do their work for them. You have been here over 10 years now and have made over 3000 posts, so I think you have the ability to understand it, if you just spend a little time with it. I will break the concepts down for you.

Just like you can have variables to hold values, you can have variable to hold objects like ranges.
So all we are doing initially is created a range variable to hold the range of the last used cell on the sheet.
That is all this does:

    Dim lastCell As Range
    Set lastCell = Range("A1").SpecialCells(xlLastCell)

Once you have that variable, you can reference various things through its properties, i.e.
lastCell.Address - returns the address of that last cell
lastCell.Row - returns the row number of that last cell
lastCell.Column - returns the column number (not letter) of that last cell

Once you have those, you can replace those pieces in any of your range references.
Just remember that literal values go in between double-quotes, and variables do not, and you join them with an "&".
So, if you wanted the range that starts in cell C1 and goes to the last cell in column C, it would look like:

Range("C1:C" & lastCell.Row)

So this should give you all that you need to do what you want. It is all right here. Please try it out for yourself (that is how you will learn!).
If something isn't quite working out for you, or you have a specific question about one of these details, please post what you have tried here and we will help you walk through it.

 

[Dazzawm]

Thanks, I will just count the rows and columns and enter into the code on each different file.

 

[Joe4]

Thanks, I will just count the rows and columns and enter into the code on each different file.

That is what the code I gave you does! It will tell you the last row and column on your sheet dynamically.
No need to do anything manually.
And I showed you exactly how to incorporate those values into the code.