Making a Filtered Range Dynamic

[sourabh_ajmera]

Hello Everybody,

This I my first post and along with that I am a beginner to VBA/Macro. I really need your expertise to help me pass through this hurdle I have been facing couple of days.
I have tried alot and went through 100's of post but wasnt able to come up with something concrete that works.

This is my code:

Sheets("TD").Select

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1" & ":" LastColumn & "18200").AutoFilter Field:=6, Criteria1:=Array( _
"Africa", "ASIA", "CEE/CIS", "Europe", "Middle East", "NORTH AMERICA"), Operator:= _
xlFilterValues
Selection.EntireRow.Delete

ActiveSheet.Range("$A$1" & ":" & LastColumn & "$1636").AutoFilter Field:=6

---------------------------------------------------------------------------------------------
The problem I am facing here is that I am trying to filter the data with some values once a week and delete it and each week there are some columns added to the file for each day of the week.
Now since, I tried finding the last column and using it and failing miserably I am doing this by using harcoded cell number like this:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1:$FD$18200").AutoFilter Field:=6, Criteria1:=Array( _
"Africa", "ASIA", "CEE/CIS", "Europe", "Middle East", "NORTH AMERICA"), Operator:= _
xlFilterValues
Selection.EntireRow.Delete

ActiveSheet.Range("$A$1:$FD$1636").AutoFilter Field:=6

Can someone please suggest me a way that I can make the column dynamic which will find out last column by it self and use it in the range for column

P.S: R1 is the row of my headers
LastColumn is the variable where I find the last column in the sheet with data

 

[sourabh_ajmera]

Got it!!....Its funny that I have been doing this for almost two days and as soon as I join this post I got it in mins. I am turning to be good in excel (just kidding)...Thanks to Mr.Excel team

Solution:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=6, Criteria1:=Array( _
"Africa", "ASIA", "CEE/CIS", "Europe", "Middle East", "NORTH AMERICA"), Operator:= _
xlFilterValues
Selection.EntireRow.Delete

ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=6


Please let me know of any more possible solutions!! and any changes in this 1 as well.
Thank you in advance. Cheers!