How to make VBA change the range automatically in the code, when changing the range used to an other cell

Question

When I set this code for instance (view below) ,and in the excel I add a new row, then de range changes instead of G9 to G10 in one case.
For that reason the macro stop working because I have to change manually the range in VBA.

is there any way that this range is modified automatically in VBA when I change it in the excel adding a row o moving the actual range?

Range("G9").Select
ActiveSheet.Range("$B$21:$R$74").AutoFilter Field:=6, Criteria1:=Range("C14")
If Range("C14") = "" Then
ActiveSheet.Range("$B$22:$R$74").AutoFilter Field:=6

Accepted Answer

If G9 is a special cell, maybe you should assign a distinct name. Right-click the G9 cell, select "Define name...", and enter a name. Then use the new name instead of "G9", for example: Range("SomeName").Select.

Or maybe you mean that G9 should be always in the last row?

Answer

Thank @Viorel
I think if I named the cell and I move it, it will work

If I want the macro to update automatically, I have this code, only when putting the name of the cell as you indicate, it does not do any action, how would it be to update when a name is given to the cell?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$4" Then  
    Call Filtros  
End If     
End Sub

I try this but it does not work

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "Name cell" Then
Call Filtros
End If
End Sub

Share via

How to make VBA change the range automatically in the code, when changing the range used to an other cell

1 additional answer

Your answer