Learning about Arrays. Why Is my code doing this?

JeffGrant

Well-known Member
Joined
Apr 7, 2021
Messages
558
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?
 
Upvote 0
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:
VBA 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...
VBA Code:
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...
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top