+ Reply to Thread
Results 1 to 4 of 4

Function or Macro to Insert empty rows above "missing" flag

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Function or Macro to Insert empty rows above "missing" flag

    Hi everyone,
    I am again in need of urgent help!

    I could not find a solution to my problem in the previous posts and I am not an excel expert.

    I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.

    I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
    Still I have to check and manually insert extra missing for every half hour missing but that's bearable.

    This formula inserts a "missing" every time it finds a gap.

    My question is: How can I insert a row above every cell with "missing"?

    How do I do that? I am not expert in excel macros....actually I know almost nothing.
    Here is also my excel sheet.

    CR1000_Meteo_20131113_2_CLEANED.xlsx

    Thanks a lot in advance!

    Best regards

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Function or Macro to Insert empty rows above "missing" flag

    Using your posted workbook...
    Select Col_B
    CTRL+F...to open the Find window
    ...Find what: missing
    ...Look in: Values (if this isn't displaying...click the Options button)
    ...Click: Find All
    ...CTRL+A...to select all matches
    ...Click: Close

    While the matched cells are still selected
    Home.Insert.Insert_Sheet_Rows

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Function or Macro to Insert empty rows above "missing" flag

    Ron I will give it a try!
    If it works it would be a great help already.
    I was trying to use the find tool but it didn't find them so, I guess, I was stupid enough to miss something..

    I will let you know!

    Sal

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Function or Macro to Insert empty rows above "missing" flag

    Ron It works!!
    And it's pretty neat!

    Sometimes the solution is just under your nose......
    Thanks a lot!

    PS
    I guess there is no simple way to find where there is more than a half hour missng like between 01:30 and 3:30?
    Anyway thanks again!

    Sal

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy rows between "STARTCOPY" & "ENDCOPY" and insert in another worksheet
    By mikeop99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2013, 10:19 PM
  2. Macro to delete all "empty" rows
    By pedrofogao21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2012, 04:41 PM
  3. Problem Combining "Paste Link" & "Insert Rows"
    By o'h3nry in forum Excel General
    Replies: 1
    Last Post: 05-14-2010, 03:15 PM
  4. If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 AM
  5. [SOLVED] Replace "insert function" with "edit formula" button in fourmula b
    By 13brian in forum Excel General
    Replies: 0
    Last Post: 08-24-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1