Solved Count continous cells with data. Insert automatically row.

DerbyDad03 November 26, 2012 at 12:16:53

✔ Best Answer

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. Sub AddNilStreet() '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 NxtChk: '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" End If '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) = "" Exit Sub End If 'Loop if not finsihed GoTo NxtChk End Sub It will then Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.