|This code makes the following assumptions:|
1 - The first record starts in A1
2 - The second records starts in A8 or A9, depending in whether the first record is complete or if it's missing the Street row.
3 - There is nothing in Column A after the last record
The idea behind this code is that every record should contain 8 rows, assuming 5 lines of data and three blank rows. Therefore, every 8th row should be empty. If an 8th row is not empty, then the current record must be missing the Street line.
For any record where the 8th row is not empty, the code will insert a row below the Name row and place Nil in Column A of the new row. It will then check the next "8th line" to see if it is empty, etc.
The code places an "End Flag" (a simple text entry) three rows after the last record so that it knows when to stop. It deletes that text when it is finished.
I strongly suggest that you try this code in a backup copy of your workbook since macros cannot easily be undone.
'Determine length of data in Column A
lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Add Flag to set end of data for counter
Cells(lastRw + 4, 1) = "End Flag"
'Initialize Row variable
rw = 1
'Every 8th row should be empty.
'If not, insert Nil after Name
If Cells(rw + 7, 1) <> "" Then
Cells(rw + 1, 1).EntireRow.Insert
Cells(rw + 1, 1) = "Nil"
'Increase Row Variable to check next set
rw = rw + 8
'Clear End Flag and Exit when done
If Cells(rw, 1) = "End Flag" Then
Cells(rw, 1) = ""
'Loop if not finsihed
It will then
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.