autofilter with dynamic range

[keyss22]

Hi

A Newby here. I've have used the macro recorder to perform certain tasks (see macro code below) but need help adjusting code to make available for a dynamic range as the macro will be used on different data sets. As you can see when I autofilter on the 2nd column it records the number of rows which it will apply again to the next set of data which may contain more rows that need to be processed. So I am trying to make the amount of rows (& possibly columns) dynamic in this operation.

Can anybody please help.
Thank you



Sub usage9macroscombined()
'
' usage9macroscombined Macro
' from start to finish
'
' usage1sort Macro
' open and sort raw usage file before copying to macro-1 template
'


'
ChDir "F:\Work-Macro"
Workbooks.Open Filename:="F:\Work-Macro\usage.xls"
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
End With
Range("D:E,I:L").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:F").Select
Range("F1").Activate
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("B1:B124"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[Joe4]

Welcome to the Board!

Here is a little example that shows how you can pull the last row and last column on your sheet, and also how to build a range off of it:

Sub Test()

    Dim lr As Long
    Dim lc As Long
    Dim rng As Range
    
'   Find last row as last column on sheet
    lr = Range("A1").SpecialCells(xlLastCell).Row
    lc = Range("A1").SpecialCells(xlLastCell).Column
    
'   Build range to filter, starting in A1 and going to last cell
    Set rng = Range(Cells(1, 1), Cells(lr, lc))
    
    MsgBox rng.Address
    
End Sub

If you aren't familiar with the Cells object, the strcuture is:
Cells(row, column)

So, Range("C100") could be written using Cells, like this:
Cells(100,3)
or
Cells(100,"C")
(you can use the column letter or numerical equivalent for the column reference).

You should be able to incorporate these techniques into your code.

 

[keyss22]

Hi Joe4,

I appreciate the assistance. I'm yet to understand the structure of writing the code so it may take me a while to utilise your information. I do understand what you are offering as reflected in your "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!". Hopefully I will develop a better grasp and use.

 

[Joe4]

So, if your last row is changing, you can use the last row calculation I showed you to dynamically capture the value if the last row number.
Then, you can change your code, so instead of using:

Range("B1:B124")

you can use:

Range("B1:B" & lr)

 

[keyss22]

Hi Joe4,

Sorry for slow reply, I actually got it to work using

Range("B:B")

Yes, I also read how to indent the code in a post. This is in excel 2016, now I have to check whether it woks in 2010 which we use at work. Thank you for your help.

 

[Joe4]

This is in excel 2016, now I have to check whether it woks in 2010 which we use at work.

Not much changed between then, and the "Range("B:B")" code is nothing special, and has been around forever.
So Excel 2010 would not have any issues with that part of the code.