Fill to LastRow Function

[shebe228]

I am trying to create a macro that inserts a specific formula into a cell and auto fills to the last row. It then is supposed to sort a range of a few columns and repeat on each worksheet in my workbook. I had this macro working until I added the last row verbiage. I am now receiving a Method 'Range" of object error on the first "With Range ("BC2:BC" & Lastrow). Any suggestions?

Sub AM4_IN_Filter_Discounts()'
' AM4_IN_Filter_Discounts
'
' Adds two new columns with if statements for less than 10%.  Sorts by Yes-Less than 10% then by Bill Count and Allowed
'
    Dim ws As Worksheet
    Dim Lastrow As Long
    
    Application.ScreenUpdating = False




    For Each ws In ActiveWorkbook.Worksheets




        With ws
            .Columns("BC:BC").Insert Shift:=xlToRight
            .Range("BC1").Value = ">10%"
 
[COLOR=#0000ff]            With Range("BC2:BC" & Lastrow)[/COLOR]
                .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
                .Value = .Value
            End With
            
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("BC2"), SortOn:=xlSortOnValues, _
                                 Order:=xlAscending, CustomOrder:="no,yes", DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=.Range("AQ2"), SortOn:=xlSortOnValues, _
                                 Order:=xlDescending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Parent.Range("AO:BC")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With


           
            With .Range("BS1").Value = ">10%"
            End With
            
                        
            With .Range("BS2:BS" & Lastrow)
                .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
                .Value = .Value
            End With




            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("BS2"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="no,yes", _
                                             DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=.Range("BH2" _
                                             ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal


            With .Sort
                .SetRange Parent.Range("BE:BS")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With


        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[Dryver14]

you need to put the line

LastRow = cells(rows.count,55).end(xlup).row

before the With ws

 

[shebe228]

Thank you! What does the 55 reference?

 

[Dryver14]

55 is column BC if you want to get the lengh of a different column to start with then use a different number a =1, b=2 etc

 

[Dryver14]

If BS is likely to be a different length to BC then you need to add the Lastrow = again using (1,71)

 

[Rick Rothstein]

you need to put the line

LastRow = cells(rows.count,55).end(xlup).row

before the With ws

Since the column is a constant (as opposed to an iterating column number), I would suggest using the column designation in place of the 55 as I find that more "self-documenting"...

LastRow = Cells(Rows.COunt, "BC").End(xlUp).Row

 

[Dryver14]

I think you are right Rick, a method I may adopt, I have always used numbers for the column since I learned from the MrExcel DVD