VBA code for inserting rows based on cell value

thr_403

New Member
Joined
Mar 26, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!

1616754089931.png
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,178
Office Version
  1. 2016
Platform
  1. Windows
I hope I understood what you wanted. Try this code
VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,741
Members
417,165
Latest member
Hilders1

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