Arrays To Compare & Delete Data

9DravenAlpha

New Member
Joined
Nov 28, 2019
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.

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

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Nov 28, 2019
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
I show you 2 options.

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

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

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

New Member
Joined
Nov 28, 2019
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi Dante,

Thank you in advance.

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

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

New Member
Joined
Nov 28, 2019
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 28, 2019
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
change this
For i = 1 To UBound(ar2)

To
Rich (BB code):
For i = 5 To UBound(ar2)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,053
Messages
5,639,792
Members
417,114
Latest member
JPFaulkner

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
Top