how does xlup -1 works in vba?

[jaik22]

Sub reva()
Dim LastRowColumnA As Long: LastRowColumnA = Sheets("Working Other Purchases").Cells(Rows.Count, 1).End(xlUp).Row




   ActiveSheet.Range("A1:AV" & LastRowColumnA).AutoFilter Field:=12, Criteria1:="=*Rate*", Operator:=xlFilterValues




    ActiveSheet.Range("A1:AV" & LastRowColumnA).AutoFilter Field:=10, Criteria1:="="
   Range("J10000", Cells(Rows.Count, "J").End(xlUp).Offset(-1)).Select


    
    ActiveCell.FormulaR1C1 = "Rate"
    Selection.FillDown
End Sub

Hi guys, I am trying to filter 12th column *Rate* and filter 10th column with "=".
Then I am trying to fill up the 10th column's with "Rate".
Code above is what I tried to put rate in the 10th column, but xlup function does not work properly

does anyone has Idea abnout this?

Thank you!

 

[wigi]

Consider:

Sub reva()    
    With Sheets("Working Other Purchases").Cells(1).CurrentRegion
        .AutoFilter Field:=12, Criteria1:="=*Rate*"
        .AutoFilter Field:=10, Criteria1:="="
        .Columns(10).SpecialCells(xlCellTypeVisible).Value = "Rate"
    End With
    
End Sub

 

[jaik22]

Hey Wigi, Thanks for your support, but If I run your code, it aslo changes the column name.
Is there any way to not include column name using SpecialCells(xlCellTypeVisible)?

Thanks!

 

[BQardi]

   Range("J10000", Cells(Rows.Count, "J").End(xlUp).Offset(-1)).Select
[/QUOTE]
Got this line working though it will fail if there is no data visible in column J. Besides, is it intentional that you want to select from row 10000 and down/up to the last row?:
[CODE]Range("J10000", Range("J" & Rows.Count).End(xlUp).Offset(-1, 0)).Select

If you want to select the whole range in the filtered table, use this instead:

Range("J2", Range("J" & Rows.Count).End(xlUp)).Select

 

[wigi]

You can use Offset(1) to go 1 row down.

 

[jaik22]

You can use Offset(1) to go 1 row down.

Sub reva3()    With Sheets("Working Other Purchases").Cells(1).CurrentRegion
        .AutoFilter Field:=12, Criteria1:="=*Rate*"
        .AutoFilter Field:=10, Criteria1:="="
        .Columns(10).SpecialCells(xlCellTypeVisible).Offset(1) = "Rate"
    End With
    
End Sub

If I do something like that It only fills up the one of the column, am I using offset correctly?

 

[wigi]

Does this do what you need?

Sub reva3()

    With Sheets("Working Other Purchases").Cells(1).CurrentRegion
        .AutoFilter Field:=12, Criteria1:="=*Rate*"
        .AutoFilter Field:=10, Criteria1:="="
        .Columns(10).Offset(1).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Offset(1) = "Rate"
    End With


End Sub

 

[jaik22]

Thank you so much for your help.

If I run that code, column I filled with rate instead filling J.
I believe second offset is causing the problem. But, if I only run first one I get one more rate in the column.

Is there any way to solve this?

Thank you!

 

[wigi]

Yes the second Offset is the issue.
You should resize the Currentregion:

With Sheets("Working Other Purchases").Cells(1).CurrentRegion
    .Resize(.Rows.Count - 1).Offset ....
End With

This could shifts the currentregion 1 row down (offset) but also removes the extra line that is induced by the offset (see the -1)

 

[jaik22]

Sweet! This was what I need Thank you!