Inserting blank rows based on condition

Hello,

I require a macro to insert blank rows in a spreadsheet based on a condition. I have attached the file to explain the problem. The spreadsheet contains data logged by an instrument. The data is mostly consistent but has certain gaps in that the data which is usually logged at 20 seconds intervals suddenly jumps by about 2-3 minutes. What I need is the following:

- Check the difference in time between two rows. If greater than 20 seconds, insert blank row. If equal to 20 seconds, leave as is.

- Increment time by 20 seconds into the blank rows (based on the time in the preceding non-blank row).

- Copy the contents of the non-blank cells in the "Value" column into the subsequent blank cells. Do this for every set of blank cells based on the preceding non-blank cell.

To illustrate, I have shown what I need in the "required" sheet. The yellow cells in the "existing" sheet indicate the jumps in the data. The grey cells in the "required" sheet indicate the gaps that have been filled.

This is just a sample of the data. I have loads of data with each sheet running into 60,000 rows. Hence please let me know what modifications I need to do in the code to enable the macro to work on all possible data in the particular sheet. Assume that the sheet only has 2 rows i.e the time and the value but could have about 60,000 rows.

Any help is greatly appreciated.

Cheers

I have opened another sheet called sheet2 where I copied the data form column A and B from sheet called "existing".

I made the sheset2 as active sheet.(you need not do it. the macro does it)

now run the macro "test"

Sub test()
Dim j As Integer, k As Integer, m As Integer
j = Range("A1").End(xlDown).Row
Worksheets("sheet2").Activate
For k = j To 3 Step -1
'If Cells(k - 1, "A") = Cells(k, "A") - TimeSerial(0, 0, 20) Then GoTo nextk
'MsgBox k
m = Round((Cells(k, "A") - Cells(k - 1, "a")) / (20 / 60 / 60 / 24))

'MsgBox m
If m = 1 Then GoTo nextk
Range(Cells(k, "A"), Cells(k + m - 2, "A")).EntireRow.Insert
Range(Cells(k - 1, "A"), Cells(k + m - 2, "A")).DataSeries rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
    Step:=TimeSerial(0, 0, 20), Trend:=False
Range(Cells(k - 1, "B"), Cells(k + m - 2, "B")).FormulaArray = Cells(k - 1, "B").Value
nextk:
Next k
MsgBox "macro over"
End Sub

after running the macro I copied the columns A and B from sheet "required" and pasted it on C1 in sheet2 so that you can compare the result of the macro and required
your file now called "book 2 ANANTH.xls" is returned.

If you want to recheck the macro

do this "undo" macro given below

Sub undo()
Worksheets("sheet2").Cells.Clear
Worksheets("existing").Cells.Copy
Worksheets("sheet2").Range("A1").PasteSpecial
Application.CutCopyMode = False
End Sub

after re running the macro you again copy columns A and B from sheet "required" and paste it to D1 of sheet2 for verifying .

As you are messing up with data keep the sheet "existing" as it is and do the macro on sheet 2 so that the original data can be retrieved if there is a problem.,

your feedback please

Hi Venkat,

You have helped me yet again. Thanks a ton. It works very well. I only have one question.

If I use more than about 32,000 rows, the code gives an error saying "run time error 6: Overflow" . I used this on Excel 2007 as well, which supports more than a million rows as compared to Excel 2003 which supports only 65,536, but I get the same error. In any case, there is no chance that once the macro is run, the number of rows will increase from 32,000 to >65536 unless there is a huge number of missing data in between.

Would you be able to check this for me if it is not too much of an ask? If you need a spreadsheet with that much data to work on, it is attached for your reference.

P.S: The number 32,000 is not exact. I tried the macro with 30,000 rows, it worked. I tried it with 34,000 rows and it gave the error. SO it is some number in between.

Thanks again.

Ananth

Dear velkat1926,

I have a similar problem with data measuring as ananth22... but, in my case, the data regarding time 0:00:00 are mising!
What I need is the following:
-macro which can insert row with 0:00:00 time on every date with value = value at time 01:00:00 divided by two; and in the following action I need divide value in time 01:00:00 with two.

Any help is appreciate
Thanks in advance!