Solved

Filter Range problem in Excel

Posted on 2013-10-23
1
220 Views
Last Modified: 2013-10-23
Hi Guys, I recorded a Macro for a daily process whereby I put an auto-filter on row 3.Then do End(xlDown) and copy the region. However, the Macro recorded the exact Row range and it will be variable every day so I need to adapt it.
Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$166").AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

Any ideas?

Justin
0
Comment
Question by:Justincut
1 Comment
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
ID: 39593783
Hello Justin,

try the following:
Dim lr as long
lr = Cells(Rows.Count, "A").End(xlUp).Row

Rows("3:3").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$R$" & lr).AutoFilter Field:=13, Criteria1:=">=1", _
        Operator:=xlOr, Criteria2:="<=-1"

Open in new window

where Cells(Rows.Count, "A").End(xlUp).Row finds the last row used in column A.
If needed, replace A with a column in which there's always a value in the last row.

--
Btw, you can also leave this part out:
Rows("3:3").Select
    Selection.AutoFilter


So, this works as well:
Rows("3:3").AutoFilter
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now