insert row after specific date found

[redspanna]

Hi all

Can someone please show how I can look down column A of Sheet1 for the last time a specific date is shown - this date found from cell A1 of Sheet2
When this date is found insert a row after

so for example A1 Sheet2 = 06SEP

Sheet 1 shows before code added

06SEP
06SEP
06SEP
07SEP
07SEP
07SEP

after code added and run

06SEP
06SEP
06SEP

07SEP
07SEP
07SEP


thanks in advance

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim LookFor As Variant
    Dim r As Long
    LookFor = Worksheets("Sheet2").Range("A1").Value
    With Worksheets("Sheet1").Range("A1").CurrentRegion.Columns(1)
        For r = .Rows.Count To 1 Step -1
            With .Cells(r, 1)
                If .Value = LookFor Then
                    .Offset(1).EntireRow.Insert
                    Exit Sub
                End If
            End With
        Next r
    End With
End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Sep58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Address: [COLOR="Navy"]Next[/COLOR]
    Range(Join(.items(), ",")).Offset(1).EntireRow.Insert
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[redspanna]

Thanks Andrew , works great