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)
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 recordThe 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 SubIt will then
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.