Most Efficient Ways To Compare Multiple Array Values in VBA?

Question

Before I begin, let me say that I know this probably isn't best done in excel and VBA, but my hands are tied in that regard.

I have a spreadsheet of 29 columns (always), and anywhere between 100 and 10,000 rows each day.

I need a way to automatically move rows from one sheet to another based on multiple criteria. The basic logic is that it needs to search the first sheet, and if C OR D equal a certain text value, AND Y contains a certain numeric value, it needs to be added to the second sheet and removed from the first.

The problem is that I've built a test version that only looks at ONE of the three criteria, and it is already very slow - If I had two more arrays and if/and/or logic, I don't think this is going to be usable on the 10,000 row days.

What can I do to speed this up? I feel like there has to be a better/faster way than arrays and for loops. I'm very much a novice, so I'm sure this is a very bad solution.

Option Explicit

Sub MacroSoFar()

Dim lColumnLength As Long
Dim intArrayCounter As Integer
Dim TestArray(165) As String

TestArray(0) = "4"
TestArray(1) = "5"
TestArray(2) = "6"
TestArray(3) = "7"
TestArray(4) = "8"
TestArray(5) = "9"
TestArray(6) = "10"
TestArray(7) = "11"
TestArray(8) = "12"
TestArray(9) = "14"
TestArray(10) = "19"
TestArray(11) = "20"
TestArray(12) = "21"
TestArray(13) = "25"
TestArray(14) = "30"
TestArray(15) = "35"
TestArray(16) = "36"
TestArray(17) = "37"
TestArray(18) = "41"
TestArray(19) = "42"
TestArray(20) = "43"
TestArray(21) = "46"
TestArray(22) = "47"
TestArray(23) = "48"
TestArray(24) = "51"
TestArray(25) = "52"
TestArray(26) = "53"
TestArray(27) = "60"
TestArray(28) = "63"
TestArray(29) = "65"
TestArray(30) = "66"
TestArray(31) = "67"
TestArray(32) = "68"
TestArray(33) = "69"
TestArray(34) = "70"
TestArray(35) = "71"
TestArray(36) = "72"
TestArray(37) = "73"
TestArray(38) = "74"
TestArray(39) = "75"
TestArray(40) = "76"
TestArray(41) = "77"
TestArray(42) = "78"
TestArray(43) = "79"
TestArray(44) = "80"
TestArray(45) = "81"
TestArray(46) = "82"
TestArray(47) = "83"
TestArray(48) = "84"
TestArray(49) = "85"
TestArray(50) = "86"
TestArray(51) = "87"
TestArray(52) = "88"
TestArray(53) = "89"
TestArray(54) = "90"
TestArray(55) = "91"
TestArray(56) = "92"
TestArray(57) = "93"
TestArray(58) = "94"
TestArray(59) = "96"
TestArray(60) = "97"
TestArray(61) = "98"
TestArray(62) = "99"
TestArray(63) = "101"
TestArray(64) = "102"
TestArray(65) = "103"
TestArray(66) = "106"
TestArray(67) = "107"
TestArray(68) = "108"
TestArray(69) = "109"
TestArray(70) = "111"
TestArray(71) = "112"
TestArray(72) = "113"
TestArray(73) = "115"
TestArray(74) = "116"
TestArray(75) = "117"
TestArray(76) = "118"
TestArray(77) = "121"
TestArray(78) = "122"
TestArray(79) = "125"
TestArray(80) = "129"
TestArray(81) = "130"
TestArray(82) = "131"
TestArray(83) = "132"
TestArray(84) = "133"
TestArray(85) = "134"
TestArray(86) = "137"
TestArray(87) = "138"
TestArray(88) = "142"
TestArray(89) = "143"
TestArray(90) = "144"
TestArray(91) = "145"
TestArray(92) = "146"
TestArray(93) = "147"
TestArray(94) = "155"
TestArray(95) = "156"
TestArray(96) = "157"
TestArray(97) = "158"
TestArray(98) = "159"
TestArray(99) = "161"
TestArray(100) = "162"
TestArray(101) = "169"
TestArray(102) = "170"
TestArray(103) = "173"
TestArray(104) = "174"
TestArray(105) = "175"
TestArray(106) = "176"
TestArray(107) = "180"
TestArray(108) = "181"
TestArray(109) = "182"
TestArray(110) = "183"
TestArray(111) = "184"
TestArray(112) = "185"
TestArray(113) = "186"
TestArray(114) = "187"
TestArray(115) = "188"
TestArray(116) = "189"
TestArray(117) = "190"
TestArray(118) = "192"
TestArray(119) = "193"
TestArray(120) = "194"
TestArray(121) = "195"
TestArray(122) = "196"
TestArray(123) = "201"
TestArray(124) = "202"
TestArray(125) = "205"
TestArray(126) = "206"
TestArray(127) = "207"
TestArray(128) = "208"
TestArray(129) = "211"
TestArray(130) = "212"
TestArray(131) = "214"
TestArray(132) = "215"
TestArray(133) = "217"
TestArray(134) = "218"
TestArray(135) = "220"
TestArray(136) = "221"
TestArray(137) = "222"
TestArray(138) = "223"
TestArray(139) = "224"
TestArray(140) = "225"
TestArray(141) = "226"
TestArray(142) = "228"
TestArray(143) = "229"
TestArray(144) = "230"
TestArray(145) = "235"
TestArray(146) = "236"
TestArray(147) = "237"
TestArray(148) = "240"
TestArray(149) = "241"
TestArray(150) = "242"
TestArray(151) = "244"
TestArray(152) = "249"
TestArray(153) = "250"
TestArray(154) = "251"
TestArray(155) = "255"
TestArray(156) = "256"
TestArray(157) = "259"
TestArray(158) = "260"
TestArray(159) = "262"
TestArray(160) = "263"
TestArray(161) = "264"
TestArray(162) = "265"
TestArray(163) = "266"
TestArray(164) = "267"
TestArray(165) = "269"


For intArrayCounter = 0 To 165 Step 1
    For lColumnLength = Cells(Rows.Count, 25).End(xlUp).Row To 1 Step -1 
        If InStr(Cells(lColumnLength, 25), TestArray(intArrayCounter)) > 0 Then
            Range("a" & lColumnLength & ":AC" & lColumnLength).Copy Sheet10.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            Cells(lColumnLength, 29).EntireRow.Delete
        End If
    Next
 Next
End Sub

If I rewrite your code as an answer but am not sure if it's faster than what you have, would you be willing to test it? Also, do the TestArray values change or stay the same from day to day? — puzzlepiece87, Jan 08 '16 at 20:43
Yeah, I'd certainly be willing to test it. The array values should always be the same day to day, though they might change a number here or there every couple of months or years. — , Jan 08 '16 at 20:47
make sure you turn calculation off. I will bet that your rows have formulas that are recalculating every time you delete a line. — Scott Craner, Jan 08 '16 at 21:43
Do you want to avoid partial matches? For example, in your `TestArray` you have both `"7"` and `"70"`. The 7 will find a partial match on 70. This could be problematic if the partial match isn't actually for a number in the list. For example, searching for `"7"` on `text 735 more text` will return successful, even though `735` isn't in your `TestArray` — tigeravatar, Jan 08 '16 at 21:56
[this answer](http://stackoverflow.com/a/34564306/445425) provides two ways to do this much faster. Key point is to loop over a Variant Array copy of your data, building a range reference to all rows to be deleted and do the delete in one go at the end. In your case build another Variant Array as you go of data to be added to the other sheet and place it in one go at the end. — chris neilsen, Jan 09 '16 at 00:22
I'd like to avoid partial matches, but the problem is that the column I'm looking in will sometimes have "45", and will sometimes have "2, 45", and I would need to detect the 45 regardless, but I wouldn't even know where to begin with VBA in trying to read values between commas in the cell, especially without getting even slower. I also have to try and keep this as non-complex and easy to follow as possible, so people who know even less than I do can reasonably add or remove numbers to it in my absence. — , Jan 11 '16 at 20:28

Demetri · Accepted Answer · 2016-01-08T22:17:02.203

I haven't tested this, but I got to run.

But it may give you some food for thought.

Option Explicit

Sub MacroSoFar()

    Dim lColumnLength As Long
    Dim intArrayCounter As Integer
    Dim TestArray(165) As String

    TestArray(0) = "4"
    TestArray(1) = "5"
    TestArray(2) = "6"
    TestArray(3) = "7"
    TestArray(4) = "8"
    TestArray(5) = "9"
    TestArray(6) = "10"
    TestArray(7) = "11"
    TestArray(8) = "12"
    TestArray(9) = "14"
    TestArray(10) = "19"
    TestArray(11) = "20"
    TestArray(12) = "21"
    TestArray(13) = "25"
    TestArray(14) = "30"
    TestArray(15) = "35"
    TestArray(16) = "36"
    TestArray(17) = "37"
    TestArray(18) = "41"
    TestArray(19) = "42"
    TestArray(20) = "43"
    TestArray(21) = "46"
    TestArray(22) = "47"
    TestArray(23) = "48"
    TestArray(24) = "51"
    TestArray(25) = "52"
    TestArray(26) = "53"
    TestArray(27) = "60"
    TestArray(28) = "63"
    TestArray(29) = "65"
    TestArray(30) = "66"
    TestArray(31) = "67"
    TestArray(32) = "68"
    TestArray(33) = "69"
    TestArray(34) = "70"
    TestArray(35) = "71"
    TestArray(36) = "72"
    TestArray(37) = "73"
    TestArray(38) = "74"
    TestArray(39) = "75"
    TestArray(40) = "76"
    TestArray(41) = "77"
    TestArray(42) = "78"
    TestArray(43) = "79"
    TestArray(44) = "80"
    TestArray(45) = "81"
    TestArray(46) = "82"
    TestArray(47) = "83"
    TestArray(48) = "84"
    TestArray(49) = "85"
    TestArray(50) = "86"
    TestArray(51) = "87"
    TestArray(52) = "88"
    TestArray(53) = "89"
    TestArray(54) = "90"
    TestArray(55) = "91"
    TestArray(56) = "92"
    TestArray(57) = "93"
    TestArray(58) = "94"
    TestArray(59) = "96"
    TestArray(60) = "97"
    TestArray(61) = "98"
    TestArray(62) = "99"
    TestArray(63) = "101"
    TestArray(64) = "102"
    TestArray(65) = "103"
    TestArray(66) = "106"
    TestArray(67) = "107"
    TestArray(68) = "108"
    TestArray(69) = "109"
    TestArray(70) = "111"
    TestArray(71) = "112"
    TestArray(72) = "113"
    TestArray(73) = "115"
    TestArray(74) = "116"
    TestArray(75) = "117"
    TestArray(76) = "118"
    TestArray(77) = "121"
    TestArray(78) = "122"
    TestArray(79) = "125"
    TestArray(80) = "129"
    TestArray(81) = "130"
    TestArray(82) = "131"
    TestArray(83) = "132"
    TestArray(84) = "133"
    TestArray(85) = "134"
    TestArray(86) = "137"
    TestArray(87) = "138"
    TestArray(88) = "142"
    TestArray(89) = "143"
    TestArray(90) = "144"
    TestArray(91) = "145"
    TestArray(92) = "146"
    TestArray(93) = "147"
    TestArray(94) = "155"
    TestArray(95) = "156"
    TestArray(96) = "157"
    TestArray(97) = "158"
    TestArray(98) = "159"
    TestArray(99) = "161"
    TestArray(100) = "162"
    TestArray(101) = "169"
    TestArray(102) = "170"
    TestArray(103) = "173"
    TestArray(104) = "174"
    TestArray(105) = "175"
    TestArray(106) = "176"
    TestArray(107) = "180"
    TestArray(108) = "181"
    TestArray(109) = "182"
    TestArray(110) = "183"
    TestArray(111) = "184"
    TestArray(112) = "185"
    TestArray(113) = "186"
    TestArray(114) = "187"
    TestArray(115) = "188"
    TestArray(116) = "189"
    TestArray(117) = "190"
    TestArray(118) = "192"
    TestArray(119) = "193"
    TestArray(120) = "194"
    TestArray(121) = "195"
    TestArray(122) = "196"
    TestArray(123) = "201"
    TestArray(124) = "202"
    TestArray(125) = "205"
    TestArray(126) = "206"
    TestArray(127) = "207"
    TestArray(128) = "208"
    TestArray(129) = "211"
    TestArray(130) = "212"
    TestArray(131) = "214"
    TestArray(132) = "215"
    TestArray(133) = "217"
    TestArray(134) = "218"
    TestArray(135) = "220"
    TestArray(136) = "221"
    TestArray(137) = "222"
    TestArray(138) = "223"
    TestArray(139) = "224"
    TestArray(140) = "225"
    TestArray(141) = "226"
    TestArray(142) = "228"
    TestArray(143) = "229"
    TestArray(144) = "230"
    TestArray(145) = "235"
    TestArray(146) = "236"
    TestArray(147) = "237"
    TestArray(148) = "240"
    TestArray(149) = "241"
    TestArray(150) = "242"
    TestArray(151) = "244"
    TestArray(152) = "249"
    TestArray(153) = "250"
    TestArray(154) = "251"
    TestArray(155) = "255"
    TestArray(156) = "256"
    TestArray(157) = "259"
    TestArray(158) = "260"
    TestArray(159) = "262"
    TestArray(160) = "263"
    TestArray(161) = "264"
    TestArray(162) = "265"
    TestArray(163) = "266"
    TestArray(164) = "267"
    TestArray(165) = "269"

    Dim oSheet As Variant, nSheet As Variant, oList As New Collection, nList As New Collection

    oSheet = Range("A1:AC" & Cells(Rows.Count, 25).End(xlUp).Row).Value
    For intArrayCounter = 0 To 165 Step 1
        For lColumnLength = Cells(Rows.Count, 25).End(xlUp).Row To 1 Step -1
            If InStr(oSheet(lColumnLength, 25), TestArray(intArrayCounter)) > 0 Then
                ' Add to list in order
                nList.Add Range("a" & lColumnLength & ":AC" & lColumnLength).Value
                Else
                ' Add to list in reverse order
                oList.Add Range("a" & lColumnLength & ":AC" & lColumnLength).Value
            End If
        Next
    Next
    For i = oList.Count To 1 Step -1
        For j = 1 To 29
            oSheet(i, j) = oList(i)(1, j)
        Next j
    Next i
    Range("A1:AC" & Cells(Rows.Count, 25).End(xlUp).Row) = oSheet
    Range("A" & oList.Count + 1 & ":A" & Cells(Rows.Count, 25).End(xlUp).Row).EntireRow.Delete Shift:=xlUp

    nSheet = Sheet10.Range("A1:AC" & nList.Count).Offset(Sheet10.Range("A" & Sheet10.UsedRange.Rows.Count).End(xlUp).Row).Value
    For i = nList.Count To 1
        For j = 1 To 29
            nSheet(i, j) = nList(i)(1, j)
        Next j
    Next i
    Sheet10.Range("A1:AC" & nList.Count).Offset(Sheet10.Range("A" & Sheet10.UsedRange.Rows.Count).End(xlUp).Row) = nSheet

    Set nList = Nothing: Set oList = Nothing:Set oSheet = Nothing: Set nSheet = Nothing

End Sub

Looping over the range will never be fast. See linked answer in my comment on OP — chris neilsen, Jan 09 '16 at 00:24
@chrisneilsen - No, I am not suggesting looping over the range. I mean that the user should try to set a variant variable equal to the range, loop over the variant, and then put it back in the range. This is actually by far the fastest way to do it. But as I did not have a lot of time, and did not have the exact data, I probably made a few mistakes in the exact application. But it has been shown (and I will provide a link if I can remember where I found it) that this method is the most efficient means (provided you are not concerned about keeping formulas). — Demetri, Jan 12 '16 at 15:34
@chrisneilsen - Here's a link, just to consider: [link](http://stackoverflow.com/questions/33302962/performance-difference-between-looping-range-vs-looping-array) — Demetri, Jan 12 '16 at 15:47

Most Efficient Ways To Compare Multiple Array Values in VBA?

1 Answers1