# How to make a data set rectangular?

#### Darkblood

##### New Member
So I have a data set, the only problem is the years are not continuous (i.e. it jumps from 1920 to 1925 etc etc). I have at least a few thousands gaps to fill so was hoping there would be an easier way to add the rows and fill in the appropriate dates. I put in a formula that tells me how many rows need to be added in each current line (so for the above example it would read 5, so I need to add 4 rows and the numbers 1921-1924 in those rows). Any advice?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
This macro runs down column 1 from row 1 and inserts missing numbers

try on a copy:

Code:
``````Sub KevinFillrows()
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
If Cells(i + 1, 1).Value - Cells(i, 1).Value > 1 Then
i = i + 1
Cells(i, 1).EntireRow.Insert
Cells(i, 1).Value = Cells(i - 1, 1).Value + 1
i = i - 1
End If
Next i

End Sub``````

Replies
3
Views
178
Replies
5
Views
272
Replies
5
Views
64
Replies
4
Views
115
Replies
1
Views
97

1,203,323
Messages
6,054,727
Members
444,747
Latest member
Jaborsum

### 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.

### Which adblocker are you using?

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

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