vba,loops,excel-vba,autofilter

The Dictionary object allows you to create a list of all unique entries in Column A. Next, you can loop over the unique entries, filtering by each one. NOTE: In my code below, I've employed the CurrentRegion property of A1. This property assume that data is contiguous, without any empty...

excel,vba,dictionary,collections,autofilter

Since AutoFilter runs from an array, I would build the array dynamically and use it in a filtering sub: Dim ary() Sub MAIN() Call BuildDynamicArray Call FilterMyData End Sub Sub BuildDynamicArray() Dim inString As String i = 1 While 1 = 1 x = Application.InputBox(Prompt:="Enter a value", Type:=2) If x...

I think AutoFilter works on one column (Field:=1, or Field:=8, etc). It can accept multiple criteria but limited to the value in each field One other solution could be with formulas (if you can use a temporary column) The formula: =OR( B2="Unpaid", AND(B2="Paid", A2 < 4) ) . My Dates:...

excel,vba,excel-vba,autofilter

The "<>" isn't an operator (which is how you tried to use it in the "not working" example), but rather needs to be part of the string you are passing to the AutoFilter (which is how you did it in the working first example.) ActiveSheet.Range("$B$1:$BY$319696").AutoFilter Field:=27, Criteria1:= "<>" & myValue...

excel-vba,variables,datatable,autofilter

If there is only one Table on the new worksheet, then we don't need a variable for the ListObject Private Sub Worksheet_Change(ByVal Target As Range) With ActiveSheet.ListObjects(1) .AutoFilter.ApplyFilter End With End Sub will do the trick....

Hidden rows have Height=0 You'll have to loop over all used rows and process only the visible ones For k = 1 To TSOA.DataBodyRange.Rows.Count and If SOAwb.Worksheets(1).Cells(j, 4).Height > 0 Then... similar to this: For k = 1 To TSOA.DataBodyRange.Rows.Count With SOAwb.Worksheets(1) If TSOA.DataBodyRange(k, 6).Height > 0 Then .Cells(j, 4)...

I think there may be more than one issue at play here. The reason you are getting an error is that you can't paste anything into cells that are beyond the boundaries of the destination sheet even if the copied cells are blank. However, the error I get running your...

excel,vba,excel-vba,autofilter

This uses the Range.AdvancedFilter method further described here to filter your data, based on user input, and copies the filtered data to a second worksheet in the same workbook. Because AdvancedFilter requires some 'setting-up' the following assumptions have been made in my example. You may need to change these for...

excel,vba,excel-vba,autofilter

It doesn't look like your code is adding anything to the sheet, perhaps the empty rows are already there and you're only noticing them because of the operation? Because you're doing: .Range("A1:Q1").AutoFilter Excel is interpreting the range as that size. There are a number of ways to find the "last...

I believe you should add a Criteria2, Criteria3, and Criteria4. I would update this line ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1,_ Criteria1:=Array(a, b, c, d) To be ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1, _ Criteria1:="=" & a, _ Operator:=xlOr, _ Criteria2:="=" & b, _ Operator:=xlOr, _ Criteria3:="=" & c, _ Operator:=xlOr, _ Criteria4:="=" & d Or if you...

Replace .AutoFilter Field:=2, Criteria1:="Select All", Operator:=xlAnd, Criteria1:="<>0" with .AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd and should work...

c#,.net,excel,epplus,autofilter

This is a very good question! A traditional limitation of AutoFilter is that it can be applied only once on a worksheet. Starting with Excel 2007 you can create several Tables on a worksheet...........each with filtering capabilities. See Pieterse's Article...

excel,vba,excel-vba,autofilter

0-1-1900 is a date that does not exist. This might be the problem. Just use Criteria1:=">1/1/1900" and it should word fine....

excel,vba,datetime,excel-vba,autofilter

I found out a way: Sub DateTimeFilter() Dim d1, d2, m1, m2, y1, y2 As Integer Dim dt1, dt2 As String d1 = Day(Date - 1) d2 = Day(Date) m1 = Month(Date - 1) m2 = Month(Date) y1 = Year(Date - 1) y2 = Year(Date) dt1 = m1 & "/"...

excel,vba,excel-vba,autofilter

I know that you would like to use Month() and Year() functions within the criteria to filter the data, however I would like to propose the following solution to filter the data although the Month() and Year functions are only used to call the procedure. This procedure requires the range...

excel,vba,excel-vba,range,autofilter

I would suggest making a variable "rng" that is the entire table you are trying to filter. I will assume for this example that "rng3" is column A and "rng4" is column B. Sub Filter() Dim rng as Range Set rng = Range("A:B") rng.Autofilter 1, Criteria1:=Array( _ "CMS Part D...