VBA code for inserting rows based on cell value

[thr_403]

Hi! I'm new to VBA and am looking for a code that allows me to automatically insert rows based on a cell value. For example, I downloaded a huge csv. file of 21 years worth of data. The first column is year (2000-2020) and the second column is state followed by more columns of data. Sometimes the state doesn't have data for all 20 years so I'd like to automatically insert the years it doesn't have data so I get a consistent format for copy/pasting into another spreadsheet. My goal is to have a row for each year (2000-2020) for each state that I've downloaded data for regardless if I have data or not. Thank you for any assistance!

 

[Zot]

I hope I understood what you wanted. Try this code

Sub FillYear()

Dim strkey As String, ArryKey() As String
Dim n As Long, eRow As Long, nYear As Long
Dim state, key
Dim cell As Range, rngData As Range
Dim DictState As Object, DictData As Object
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

Set DictState = CreateObject("Scripting.Dictionary")
Set DictData = CreateObject("Scripting.Dictionary")

eRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

' Get state list
Set rngData = ws1.Range("B1", "B" & eRow)
For Each cell In rngData
    If Not DictState.Exists(cell.Value2) Then
        DictState.Add cell.Value2, Nothing
    End If
Next

Set rngData = ws1.Range("A1", "A" & eRow)
' Collect all available states data
For Each cell In rngData
    strkey = cell.Value2 & "," & cell.Offset(0, 1).Value2
    If Not DictData.Exists(cell.Value2 & "," & cell.Offset(0, 1).Value2) Then
        DictData.Add strkey, cell.Offset(0, 2).Value2
    End If
Next

n = 0
For Each state In DictState
    For nYear = 2000 To 2020
        n = n + 1
        key = nYear & "," & state
        If DictData.Exists(key) Then
            ArryKey = Split(key, ",")
            ws2.Range("A" & n) = ArryKey(0)
            ws2.Range("B" & n) = ArryKey(1)
            ws2.Range("C" & n) = DictData(key)
        Else
            ws2.Range("A" & n) = nYear
            ws2.Range("B" & n) = state
        End If
    Next
Next

End Sub