• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically fill in the missing numbers and add rows

BIOKEKEC

New Member
Hi! I need to fill in the missing numbers in numbers in column A however I need to add the rows as well. Adding them manually is not really an option as the data file has 19k+ rows. The numbers represent minutes in a day, hence they rage from 0 to 1440. Thanks in advance for the help
 

Attachments

p45cal

Well-Known Member
I doubt your sample sheet is representative; 19k rows with numbers only ranging from 0 to 1440. Either there are lots of repeats or there are multiple series.
Try the code below on about a hundred rows only to start with (it could be slow). Before you run the code, select a range on the sheet where the numbers are (no blanks!), one column wide. The code runs on what you select.
It's raw because I have precious little information to work with.
Code:
Sub blah()
Set myrng = Selection
For rw = myrng.Rows.Count To 2 Step -1
  Set clle2 = myrng.Rows(rw).Cells(1)
  diff = clle2.Value - clle2.Offset(-1).Value
  If diff > 1 Then
    Set clle1 = clle2.Offset(-1)
    clle2.EntireRow.Resize(diff - 1).Insert
    Range(clle1, clle2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
  End If
Next rw
End Sub
 
Top