Arrays To Compare & Delete Data

[9DravenAlpha]

I have two tables that are on different tabs. One tab is called RawData and the Other is called CSheet. On CSheet, I have a column of data starting in C2. I am needing to compare the CSheet data list to the table that I have on the Raw Data tab. The table on the RawData tab starts in Cell A1 and has headers. Its only a 4x4 table for now, but it will eventually include over 4,000 rows of data that goes into column AJ. The data is updated everyday so I need the code to be able to pick up dynamic ranges.

I am trying to solve his using Arrays, but I am new to them. Below is code that I've tried to so far but its not deleting all of the values in a row.

Sub DashDataTransfer()

    Dim CSheet As Variant
    Dim RawData As Variant
    Dim i, j, lRow As Long
    Dim rng As Range


    Sheet4.Activate
    CSheet = Range("C2", Range("C2").End(xlDown))
    
    Sheet1.Activate
    'RawData = Range("C2", Range("C2").End(xlDown))
    Set rng = Sheet1.Cells(2, 1).CurrentRegion
    
    RawData = rng
    
    For i = 2 To UBound(RawData, 2)
        For j = 2 To UBound(CSheet)
            If CSheet(j, 1) = RawData(i, 1) Then
            RawData(1, j) = ""
            End If
        Next j
    Next i
    
    rng = RawData
    
    'rng.Sort rng.Columns(1), xlDescending
    
    
End Sub

 

[DanteAmor]

If CSheet(j, 1) = RawData(i, 1) Then

You want to compare column C of CSheet against column A of RawData, if it finds what you need. You did not explain it and it is not clear to me what the final objective is.

You can complement your information
In column C of CSheet do you have unique values?
In column A of RawData do you have unique values?

 

[9DravenAlpha]

Hi Dante,
Thank you in advance for your reply back.

The data located on CSheet will have unique values or #NA. The values on that table will be compared to the data on RawData. When a match is made, the whole row will be deleted.

After all of the deletions are made the remaining data will be sorted so that a table of data that doesn’t have any blank rows remains.

I can do this with For Loops. Now I want to learn how to code using arrays. I have looked all over but I can’t recognize something that works.

 

[DanteAmor]

I show you 2 options.

In this option, load the cSheet data into an array, filter rawData and delete.

Sub FilterData_1()
  Dim arr As Variant
  
  arr = Sheets("CSheet").Range("C2", Sheets("CSheet").Range("C" & Rows.Count).End(3)).Value2
  With Sheets("RawData")
    .Range("A1").AutoFilter 1, Application.Transpose(arr), xlFilterValues
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .Range("A1").AutoFilter
  End With
End Sub

_________________________________________________________________________________________________________
In this option, load the cSheet data into an array, put it in a dictionary to have an "index" and make the search faster.
Load the rawData data into an array2; Loops through every data in the array2 if it exists in the dictionary adds it to the range (this process is faster in memory than deleting row by row).
At the end it deletes all the rows set in the range.

Sub FilterData_2()
  Dim ar1 As Variant, ar2 As Variant, dic As Object
  Dim lr As Long, i As Long, rng As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  ar1 = Sheets("CSheet").Range("C2", Sheets("CSheet").Range("C" & Rows.Count).End(3)).Value2
  For i = 1 To UBound(ar1)
    If Not IsError(ar1(i, 1)) Then dic(ar1(i, 1)) = Empty
  Next
  With Sheets("RawData")
    lr = .Range("A" & Rows.Count).End(3).Row
    Set rng = .Range("A" & lr + 1)
    ar2 = .Range("A1", .Range("A" & Rows.Count).End(3)).Value2
    For i = 1 To UBound(ar2)
      If dic.exists(ar2(i, 1)) Then Set rng = Union(rng, .Range("A" & i))
    Next
    rng.EntireRow.Delete
  End With
End Sub

 

[9DravenAlpha]

Hi Dante,

Thank you in advance.

Using the code that you wrote above on some sample test data... It Works!

Tomorrow I will going to run it on some larger simulation test data and will keep you posted. The difference between the For Loops and Arrays I've heard is speed and this is certainly proven that out. The code flys on through the test data.

Thank you very much
Draven

 

[9DravenAlpha]

Hi Dante,

Thank you in advance for reading this post.

I've tried the FilterData_2 code on a simulated production file, and ran into some issues because the production file is setup differently from the post I wrote initially. The RawData in the production file starts in Cell A5 and goes across to Column AH The number of rows in the RawData table will vary from day to day. Column A has no blank cells. So running an End type of counter onto that column will ensure that it catches everything. Column M of the RawData table production file is the column that the CSheet data list will look at for matches. Column M of the RawData production file is coded with a concatenate formula. The data in M starts on M5.

So I think these are tripping things up a bit, because when a copy your FilterData_2 code into a brand new file, copy the production file data into that brand new file (so that it matches up to what I had posted above,) your code works. I tried to adjust your code around a bit to work with simulated production file but didn't get it work because I am not familiar with arrays or dictionary list objects.

Please advise
Draven

 

[DanteAmor]

On CSheet, I have a column of data starting in C2

That column didn't change?

Column M of the RawData table production file is the column that the CSheet data list will look at for matches.

Try this:

Sub FilterData_3()
  Dim ar1 As Variant, ar2 As Variant, dic As Object
  Dim lr As Long, i As Long, rng As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  ar1 = Sheets("CSheet").Range("C2", Sheets("CSheet").Range("C" & Rows.Count).End(3)).Value2
  For i = 1 To UBound(ar1)
    If Not IsError(ar1(i, 1)) Then dic(ar1(i, 1)) = Empty
  Next
  With Sheets("RawData")
    lr = .Range("A" & Rows.Count).End(3).Row
    Set rng = .Range("A" & lr + 1)
    ar2 = .Range("M1:M" & lr).Value2
    For i = 1 To UBound(ar2)
      If dic.exists(ar2(i, 1)) Then Set rng = Union(rng, .Range("A" & i))
    Next
    rng.EntireRow.Delete
  End With
End Sub

 

[9DravenAlpha]

Hi Dante,

Thank you in advance for reading and responding to the posts.

I tried the code on the simulated production file and it worked! It deleted the rows that had matches to the CSheet. The final tweak would be to get it to handle the header row on row 4. Row 4 has a header row and right now its getting deleted along with the data being deleted. The code flys through the data and deletes everything it should and leaves everything in place.

Thank you
Draven

 

[DanteAmor]

change this
For i = 1 To UBound(ar2)

To

For i = 5 To UBound(ar2)