Learning about Arrays. Why Is my code doing this?

[JeffGrant]

Hi All,
I have started to learn about arrays and how they can speed up my model.
With the code below, I am looking to delete rows where the Date is older than "Date - 29". No drama.
The array is read in from shErrorIn (sheet2) and written out to shErrorOut (Sheet3). No drama.
What is happening is that on the first pass, the arrany is read in from shErrorIn but it is also written out to the same sheet.
Then running the code a second time, the array is written out to shErrorOut, where I want it.
I dont understand why the output array is being written back to the input sheet because I want to preserve the data on the input sheet.
Thanks for yoru guidance.

Sub ReadRangeError()
    Dim rg As Range
    Dim arr As Variant
    Dim rowCount As Long, columnCount As Long, i As Long

    Set rg = shErrorIn.Range("A1").CurrentRegion
    arr = rg.Value

    shErrorOut.Range("A1").CurrentRegion.ClearContents

    rowCount = UBound(arr, 1)
    columnCount = UBound(arr, 2)    
    For i = rowCount To 2 Step -1
            If arr(i, 6) <= Date - 29 Then
            Rows(i).EntireRow.Delete
            End If
    Next i
    
    shErrorOut.Range("A1").Resize(rowCount, columnCount).Value = arr

End Sub

 

[Anthony47]

With the code below, I am looking to delete rows where the Date is older than "Date - 29". No drama.
The array is read in from shErrorIn (sheet2) and written out to shErrorOut (Sheet3). No drama

Do you mean removing lines in sheet2 or avoiding that those lines are copied to sheet3?

Are shErrorIn and shErrorOut the codenames for sheet2 and sheet3 (in this order) or shErrorIn and shErrorOut are "set" somewhere else in your code?

Which sheet is selected when the macro is started?

 

[igold]

To be clear- You want to write shErrorIn to your array, you then want to delete certain rows and then write the changed data out to ShErrorOut.

If this is the case then this row of your code:

Rows(i).EntireRow.Delete

does not touch any data in the array arr, so when you write arr to shErrorOut, you are writing back data that is untouched by your code.
If all this makes sense (and is correct) then there are many ways to accomplish what you want. Not tested...

Sub ReadRangeError()

    Dim arr As Variant, arr2 As Variant
    Dim rowCount As Long, columnCount As Long, i As Long, x As Long, ct As Long
    Dim wsIN As Worksheet, wsOUT As Worksheet
  
    Set wsIN = Worksheets("shErrorIn")
    Set wsOUT = Worksheets("shErrorOut")
    arr = wsIN.Range("A1").CurrentRegion
    ReDim arr2(1 To UBound(arr, 1), 1 To UBound(arr, 2))
    wsOUT.Range("A1").CurrentRegion.ClearContents
    ct = 1
    rowCount = UBound(arr, 1)
    columnCount = UBound(arr, 2)
    For i = 1 To UBound(arr)
            If Not arr(i, 6) <= Date - 29 Then
                For x = 1 To columnCount
                    arr2(ct, x) = arr(i, x)
                Next
                ct = ct + 1
            End If
    Next i
  
    wsOUT.Range("A1").Resize(rowCount, columnCount).Value = arr2

End Sub

As @Anthony47 asked above- what sheet a running the code from. With the code above you should be on shErrorIn when the code is run...

 

[JeffGrant]

Hi igold and Anthony47,

Apart from this one modification
Set wsIN = Worksheets("sheet2")
Set wsOUT = Worksheets("sheet3")

the code you put together works perfectly. Thank you for that.

I can see what you are doing and sort of understand why you have do it that way. Is there any anychace that you can refer a place where I can learn more. Most of the videos on YouTube cover only the mear basics. What you have done is more thna the mere basics
Thanks again

 

[igold]

You're welcome. We were both happy to help. Thanks for the feedback!
I don't know any place where you may gain a better working knowledge of arrays. I taught myself just by the repetition of writing them.
Basically my code is taking arr and writing it to arr2 but leaving out the elements that meet your criteria, so it is not really a deletion but rather a failure to re-write the data. I hope that makes sense.