need macro insert row under specific data

gogita_79 · #1 09-14-2014, 05:26 AM

hi, I need macro that helps me insert one blank row under data 1410.01 lighted yellow. thanks

excelledsoftware · #2 09-14-2014, 09:29 AM

This simple snippet should do it. Just set the EvalCol to the column letter containing Gel and 1410.01.

Code:

Sub InsertRow()
  Dim CheckRow As Long, EndRow As Long, CheckString As String, EvalCol As String
  
  
  EvalCol = "G" 'Set this to the column you need to evaluate keep it in quotes
  

  EndRow = Range(EvalCol & 50000).End(xlUp).Row
  
  For CheckRow = 2 To EndRow
    CheckString = Range(EvalCol & CheckRow).Value
    If CheckString = "1410.01" Then
      Range(CheckRow + 1 & ":" & CheckRow + 1).Insert
    End If
  Next CheckRow
  
End Sub

macropod · #3 09-15-2014, 01:48 AM

An alternative approach:

Code:

Sub InsertRows()
Dim lRow As Long, i As Long
With ThisWorkbook.Worksheets("Sheet1").UsedRange
  For i = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
    If .Range("G" & i).Value = "1410.01" Then
       While .Range("G" & i + 1).Value <> ""
          .Range("G" & i + 1).EntireRow.Insert
       Wend
     End If
  Next
End With
End Sub

This approach ensures there is only one blank row (i.e. it won't insert one if there already is one).

Note: The code assumes the "1410.01" is in column G. Change the 'G' in the three '.Range("G"' references to suit.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro Needed to bold specific lines and Macro to turn into CSV	anewteacher	Word VBA	1	05-28-2014 03:59 PM
how to create menu shortcut to insert specific picture	msworddave	Word	1	05-08-2013 02:00 AM
Linking: Insert > Pictures to a specific folder location	hockfam	PowerPoint	0	11-08-2012 08:09 PM
Match two sets of data and display specific data	lolly150	Excel	1	05-14-2012 10:33 PM
Insert an item to excel specific cell	apjneeraj	Excel	0	01-18-2011 03:39 AM