+ Reply to Thread
Results 1 to 15 of 15

how can i insert a blank row automatically into excel when a number is missing

  1. #1
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    how can i insert a blank row automatically into excel when a number is missing

    Hi

    After alot of searching I thought Id have to ask the question as I cant find what I need. I am familar with Excel but not using macros too much. I am looking to achieve the following:

    I receive a report with a list of 3 columns of info in sequence. When no volume is recorded in one of the lines it is missing.

    A B C
    101 Workcode 101 6
    102 Workcode 102 26
    103 Workcode 103 32
    105 Workcode 105 42
    106 Workcode 106 28
    107 Workcode 107 5

    ie...line 104 Workcode 104 is missing.

    How can I set up a template (with macro I guess) so when I paste the file I receive into the master file in Excel that it automatically adds in a blank row when a line is missing.

    I can get it with conditional formatting (deleing duplicates etc) but I dont want to have to format the file seperately each time.

    Column A & Bs data never changes. The data in column C will change. The sequence goes from 101 Workcode 101 until 133 Workcode 133.

    Its Excel 2010

  2. #2
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    I assume the sheet name as "Sheet1"(you can change the sheet name in the code)
    Put a button on the sheet and assign the sub name to it and click the button
    it will do the neccessary change required

    Sub insertRow()

    Dim ws As Worksheet, rng As Range, i As Integer, j As Integer

    Set ws = Sheets("Sheet1") 'Change the sheet name here
    Set rng = ws.Range("A1")

    i = 1
    Do While (rng.Offset(0, j).Value <> "")
    If VBA.UCase(rng.Offset(0, j).Value) = VBA.UCase("A") Then
    Do While (rng.Offset(i, j).Value <> "")
    If rng.Offset(i, j).Value <> i + 100 Then
    Rows(i + 1).Select
    Selection.Insert Shift:=xlDown
    End If
    i = i + 1
    Loop
    End If
    j = j + 1
    Loop

    End Sub



    Piyush

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    I assume sheet name as "Sheet1"
    Insert a button on the sheet and assign macro "insertRow" and then click it

    Sub insertRow()

    Dim ws As Worksheet, rng As Range, i As Integer, j As Integer

    Set ws = Sheets("Sheet1") 'can change sheet name here
    Set rng = ws.Range("A1")

    Do While (rng.Offset(0, j).Value <> "A")
    j = j + 1
    Loop

    i = 1
    Do While (rng.Offset(i, j).Value <> "")
    If rng.Offset(i, j).Value <> i + 100 Then
    Rows(i + 1).Select
    Selection.Insert Shift:=xlDown
    End If
    i = i + 1
    Loop

    End Sub

    Piyush

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: how can i insert a blank row automatically into excel when a number is missing

    Another one:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: how can i insert a blank row automatically into excel when a number is missing

    Quote Originally Posted by piyushanand View Post
    I assume sheet name as "Sheet1"
    Insert a button on the sheet and assign macro "insertRow" and then click it

    Sub insertRow()

    Dim ws As Worksheet, rng As Range, i As Integer, j As Integer

    Set ws = Sheets("Sheet1") 'can change sheet name here
    Set rng = ws.Range("A1")

    Do While (rng.Offset(0, j).Value <> "A")
    j = j + 1
    Loop

    i = 1
    Do While (rng.Offset(i, j).Value <> "")
    If rng.Offset(i, j).Value <> i + 100 Then
    Rows(i + 1).Select
    Selection.Insert Shift:=xlDown
    End If
    i = i + 1
    Loop

    End Sub

    Piyush
    Thanks for the quick reply.Havet gotten it to work yet. Maybe Ill have to try tomoorro morning with a fresh head.

  6. #6
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: how can i insert a blank row automatically into excel when a number is missing

    Quote Originally Posted by JOHN H. DAVIS View Post
    Another one:

    Please Login or Register  to view this content.
    Thanks. This one only added 1 row even when I was missing more than 1 row from the sequence. ie...1,2,3,6 only added 'row 5' and not also 'row 6'

    I had tried the below code but it always turned the A column to 1-33 (should of stayed 101-133 etc), I dont have the skill to change it.

    Sub test_v2()
    Dim i As Long, j As Long
    i = Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    For j = i To 1 Step -1
    If Cells(j + 1, 1) <> "" Then
    If Cells(j + 1, 1).Value - Cells(j, 1).Value > 1 Then
    x = Cells(j + 1, 1).Value - Cells(j, 1).Value
    Rows(j + 1 & ":" & x + j - 1).Insert
    End If
    End If
    Next j
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=Row()"
    .Value = .Value
    End With
    Application.ScreenUpdating = True
    End Sub

  7. #7
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: how can i insert a blank row automatically into excel when a number is missing

    Quote Originally Posted by JOHN H. DAVIS View Post
    Another one:

    Please Login or Register  to view this content.
    Thanks. This one only added 1 row even when I was missing more than 1 row from the sequence. ie...1,2,3,6 only added 'row 5' and not also 'row 6'

    I had tried the below code but it always turned the A column to 1-33 (should of stayed 101-133 etc), I dont have the skill to change it.

    Sub test_v2()
    Dim i As Long, j As Long
    i = Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    For j = i To 1 Step -1
    If Cells(j + 1, 1) <> "" Then
    If Cells(j + 1, 1).Value - Cells(j, 1).Value > 1 Then
    x = Cells(j + 1, 1).Value - Cells(j, 1).Value
    Rows(j + 1 & ":" & x + j - 1).Insert
    End If
    End If
    Next j
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=Row()"
    .Value = .Value
    End With
    Application.ScreenUpdating = True
    End Sub

  8. #8
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    Quote Originally Posted by seanduffy1 View Post
    Thanks. This one only added 1 row even when I was missing more than 1 row from the sequence. ie...1,2,3,6 only added 'row 5' and not also 'row 6'

    I had tried the below code but it always turned the A column to 1-33 (should of stayed 101-133 etc), I dont have the skill to change it.

    Sub test_v2()
    Dim i As Long, j As Long
    i = Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    For j = i To 1 Step -1
    If Cells(j + 1, 1) <> "" Then
    If Cells(j + 1, 1).Value - Cells(j, 1).Value > 1 Then
    x = Cells(j + 1, 1).Value - Cells(j, 1).Value
    Rows(j + 1 & ":" & x + j - 1).Insert
    End If
    End If
    Next j
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=Row()"
    .Value = .Value
    End With
    Application.ScreenUpdating = True
    End Sub
    For me it is working very fine.
    I am attaching file.


    insertRowOnCondition.xlsm

  9. #9
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: how can i insert a blank row automatically into excel when a number is missing

    Hi again,

    Ive tried it and same result. The button only replaces 1 of the missing rosw in a sequence per click. If I am missing 3 & 4 from 1,2,3,4,5 then I need to click the button 2 times to replace both row 3 and row 4.

    What could I be doing wrong?>

    Many thanks for the help.

  10. #10
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    try this its working..


    Sub insertRow()

    Dim ws As Worksheet, rng As Range, i As Integer, j As Integer

    Set ws = Sheets("Sheet1") 'can change sheet name here
    Set rng = ws.Range("A1")

    Do While (rng.Offset(0, j).Value <> "A")
    j = j + 1
    Loop

    i = 1
    Do While (rng.Offset(i, j).Value <> "")
    If rng.Offset(i, j).Value <> i + 100 Then
    Rows(i + 1).Select
    Selection.Insert Shift:=xlDown
    End If
    i = i + 1
    Loop

    End Sub
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: how can i insert a blank row automatically into excel when a number is missing

    Perfect thats worked correctly that time. Can I use this Macro for all sheets without having to rename them? I have 52 tabs with the same info on each. I will paste in a report each week to a new tab and press the button.

    can I use this code for all the tabs (without re-naming the code each time to KW1, KW2, KW3 etc) or does the code needed changed ?

  12. #12
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    add an extra sheet at the end and create a drop down with all thye sheet name and the command button beside it
    on selection it will work for particular sheet only



    I will attach the file sometime today...

  13. #13
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    add an extra sheet at the end and create a drop down with all thye sheet name and the command button beside it
    on selection it will work for particular sheet only



    I will attach the file sometime today...

  14. #14
    Registered User
    Join Date
    10-26-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: how can i insert a blank row automatically into excel when a number is missing

    try this
    open the list sheet and selet the sheet from dropdown in E4 and then click the button
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    02-18-2013
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: how can i insert a blank row automatically into excel when a number is missing

    perfect. Thank you for the help. Much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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