0

So I'm trying to essentially filter my data set in a certain way. There may be a better way of doing this and I may be over complicating things, so if that's the case, feel free to chime in any ideas. So, essentially I'm filtering my data set. However based on that filter, I will only take some specific data based on a criteria. I will therefore be hiding the data that don't fit that criteria, which is shown here: 

If Sheet1.Cells(i, 11) = StandardW And Sheet1.Cells(i, 32) > Date1 Then
    Rows("i:i").Select
    Selection.EntireRow.Hidden = True

I will then be copying the first column of my data set and pasting it elsewhere to use it afterwards. However, for some reason, I keep getting a "Run time error 6 overflow error message." Not sure why. Any ideas? As mentioned above, maybe there's a simpler way of arranging my data around that I haven't thought about. If that's the case, feel free to chip in.

Thanks! 

Sub CopyingCodesCALG()
    Dim Standard As Date
    Dim i As Integer
    Dim lastrow As Long

    lastrow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
    Standard = Date - 3
    StandardW = Date - 2
    Date1 = Date - 1
    'this utilizes Activesheets, may have to find a workaround for this depending on the data. Maybe add it to the MF and use thisworkbook?

    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:= _
        "Livraison"
    ActiveSheet.UsedRange.AutoFilter Field:=11, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Standard, 2, StandardW)
    ActiveSheet.UsedRange.AutoFilter Field:=25, Criteria1:= _
        "=Return to warehouse", Operator:=xlOr, Criteria2:="="
    ActiveSheet.UsedRange.AutoFilter Field:=17, Criteria1:=Array( _
        "Data received", "Data received - shipment not received", "Loaded", "Loading", _
        "Optimized", "Received", "="), Operator:=xlFilterValues
    ActiveSheet.UsedRange.AutoFilter Field:=34, Criteria1:="="
    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:= _
        "INTLCM-CALG"

    i = 2

    For i = 2 To lastrow
        If Sheet1.Cells(i, 11) = StandardW And Sheet1.Cells(i, 32) > Date1 Then
            Rows("i:i").Select
            Selection.EntireRow.Hidden = True
        End If
    Next i

    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Joel Bastien
  • 121
  • 2
  • 11
  • 1
    If use the advice in [THIS POST](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) you will shorten your code and probably fix the error. – Scott Craner Jan 11 '19 at 17:48
  • Your whole If inside the Loop can be replaced with: `sheet1.Rows(i).Hidden = Sheet1.Cells(i, 11) = StandardW And Sheet1.Cells(i, 32) > Date1` – Scott Craner Jan 11 '19 at 17:51
  • @ScottCraner The OP's loop will keep any already hidden rows hidden. With your code they will be shown. – GSerg Jan 11 '19 at 17:54
  • @GSerg that is true. okay replace the two lines in the IF with `Sheet1.Rows(i).Hidden = True` – Scott Craner Jan 11 '19 at 17:55
  • 1
    @JoelBastien On which line do you get the error? – GSerg Jan 11 '19 at 17:57
  • 2
    It's good practice never to use `Integer` - `Long` is safer and there's no performance price to pay. – Tim Williams Jan 11 '19 at 18:08
  • 1
    What's `Rows("i:i")`? That's not a valid use of either the variable, or the row. I think you mean `Rows(i & ":" & i).Select` (or even just `Rows(i).Select`)? ...Also, it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/) – BruceWayne Jan 11 '19 at 18:29
  • The number of rows in a spreadsheet in modern Excel is enough to overflow an integer variable, which is one reason why the suggestion of @TimWilliams is so important. Your code probably has some other issues, but using `Dim i As Long` might fix the overflow problem. – John Coleman Jan 11 '19 at 18:41
  • @GSerg Sorry should've mentioned. Here is where I get my error: For i = 2 To lastrow – Joel Bastien Jan 11 '19 at 18:41
  • @BruceWayne , you're right It's meant to just be Rows(i).Select ... Thanks for pointing that out! – Joel Bastien Jan 11 '19 at 18:43
  • 2
    @JoelBastien There can be [1048576 rows on a sheet](https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3). [`Integer`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/integer-data-type) is from `-32768` to `32767`. – GSerg Jan 11 '19 at 18:45

0 Answers0