Solved Count continous cells with data. Insert automatically row.

November 26, 2012 at 11:09:46
Specs: Windows 7

HI .. i have a Data for entry.. in which i have data in this order

Name (ALpabetic)
Street (Alpha Numeric)
Adress (Alpha Numeric)
Telephone (Numeric)
Category (alphabetic)

this is followed by 3 empty rows.. so in total 8 rows.. 5 with data.. then 3 empty rows..the format goes on.. till i have 3000 records.. here is the question..

going down.. some RECORD (like 1 set of 5 rows of data) only has 4 rows.. it does not Have the Street Field.. for some analysis reason.. i want all 3000 records data to be equal..

1. How can i count within this 3000 records.with spaces (the 3 empty rows).. how many Records are 5 rows and how many are 4 rows..

2. For every Record with 4 rows.. i need to Insert a ROW below the NAME row and write in that the word (NIL)

See More: Count continous cells with data. Insert automatically row.

Report •

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

Report •
Related Solutions

Ask Question