VBA Modification

[Usercode]

Hi everybody,

I have this VBA code which works on the data starting from Row2 of worksheet. Instead of Row2, I want it to start from row5. I tried to change it a little but I was not successful. How can i do this? please. thanks.

Sub Intervals()    Dim R As Range, C As Range
    With Cells(1).CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1)
            For Each R In .Cells
                Set C = .Find(R.Value, R, , 1, , , 2)
                If (C.Address <> R.Address) * (C.Row > R.Row) Then
                    R.Offset(, 7) = C.Row - R.Row - 1
                Else
                    R.Offset(, 7) = "na"
                End If
            Next
        End With
    End With
End Sub

 

[MickG]

Try:-

 With .Offset(4).Resize(.Rows.Count - 4)

 

[Usercode]

MickG, I tried it but it didn't work. It gives Run time error 1004.

 

[MickG]

Have you got as blank row between row 1 and 4 ????

 

[Usercode]

Yes, row1-row4 are blanks. i want to keep them blank.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Feb37
[COLOR="Navy"]Dim[/COLOR] R as range, c [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Range("A5").CurrentRegion
       MsgBox .Cells.Address '[COLOR="Green"][B]Just to show you the Address of Currentregion.!![/B][/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Cells
                [COLOR="Navy"]Set[/COLOR] c = .Find(R.Value, R, , 1, , , 2)
                [COLOR="Navy"]If[/COLOR] (c.Address <> R.Address) * (c.Row > R.Row) [COLOR="Navy"]Then[/COLOR]
                    R.Offset(, 7) = c.Row - R.Row - 1
                [COLOR="Navy"]Else[/COLOR]
                    R.Offset(, 7) = "na"
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR]
       [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Usercode]

Thanks MickG. This is a lot simpler for me

 

[MickG]

Your welcome