Q:

Nested For Loops locking up Excel

I built a COVID model using UVA data. That data is currently unavailable so I've found another data source. Of course, the new data is not in the same format. So rather than starting from scratch, I'm importing the new data in the format the model was coded for. The new data looks like this...

The new data files are broken up into a Confirmed file, a Deaths file and a Recovered file. Each file is 267 rows and goes out to column EH with a new column added daily. I have a macro for each file. The Confirmed file takes about a minute - minute and a half to run. And when done it looks like this...

I don't import any of the confirmed = 0 data points. I've added the population/pop density columns to delete all the countries I don't care about (those with population = 0). This file has about 6800 rows and of course, this will also grow daily. When the Confirmed file is imported, of course the next thing is Deaths. When I try to import the Deaths file excel goes to 'Not Responding' state and doesn't finish. I've waited on the Deaths macro for about an hour before I killed it and it's still not done. I can see that the correct numbers are being put into the correct column in my consolidated file but it really shouldn't take so long or lock up excel like this. I realize that three For Loops is going through a lot of cells a lot of times. Is there a better way to do this?
Here is the Deaths import code:
Sub ImportCSSEDeaths()

Dim i As Variant
Dim j As Variant
Dim k As Variant
Dim lastrow As Long
Dim clastrow As Long
Dim lastcol As Long
Dim dte As Date
Dim filePath As String
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim cws As Excel.Worksheet

Set cws = ThisWorkbook.Sheets("Raw_Data")

lastrow = cws.Cells(Rows.count, "a").End(xlUp).Row

filePath = "C:\Users\chris.h\Desktop\COVID\Other_Data\CSSE\CSSE_Deaths.xlsx"

Set wb = Excel.Workbooks.Open(filePath)
Set ws = wb.Worksheets(1)

clastrow = cws.Cells(Rows.count, "b").End(xlUp).Row

lastrow = ws.Cells(Rows.count, "b").End(xlUp).Row
lastcol = ws.Cells(1, Columns.count).End(xlToLeft).Column

For i = 2 To clastrow
    For j = 2 To lastrow
        For k = 3 To lastcol

            If cws.Cells(i, "a").Value = ws.Cells(j, "a").Value And _
            cws.Cells(i, "b").Value = ws.Cells(j, "b").Value And _
            cws.Cells(i, "c").Value = ws.Cells(1, k).Value Then

                cws.Cells(i, "e").Value = ws.Cells(j, k).Value
                cws.Cells(i, "e").NumberFormat = "#,##0"
            End If
        Next k
    Next j
Next i
wb.Close False

End Sub

If more code is needed, I'll provide.

A:

Thanks to @Tom and @Nacorid the following array based code works much faster! Only takes a min or two.
k = 0
For i = 2 To lastrow
    For j = 3 To lastcol

        'puts country row deaths into array
        If ws.Cells(i, j).Value <> 0 Then
            ReDim Preserve deaths(k)
            deaths(k) = ws.Cells(i, j).Value
            k = k + 1
        End If
    Next j

    'finds startdate in new data
    For j = 3 To lastcol

        If deaths(0) = ws.Cells(i, j).Value Then
            startDate = ws.Cells(1, j).Value
            Exit For
        End If
    Next j

    Debug.Print startDate

    clastrow = cws.Cells(Rows.count, "b").End(xlUp).Row

    'finds startdate in compiled data and enters array values down column e
    For j = 2 To clastrow
        If cws.Cells(j, "a").Value = ws.Cells(i, "a").Value And _
        cws.Cells(j, "b").Value = ws.Cells(i, "b") And _
        cws.Cells(j, "c").Value = startDate Then

            count = j
            For k = 0 To UBound(deaths)
                cws.Cells(count, "e").Value = deaths(k)
                count = count + 1
            Next k
        End If
    Next j
Next i

