VBA help

[Emmily]

Hi, i need a vba code that will look in Col A5:A, if no data is found under neath the header "Group" then delete entire row. The desired result is shown in the second screenshot

Excel Workbook
	A
5	Group
6	BHO
7	BHO
8	BHO
9	BHO
10
11	Group
12
13	Group
14	RIO
15	RIO
16	RIO
17
18	Group
19
20	Group
21
22	Group
23	CBA
24	CBA
25	CBA
26	CBA
Sheet1

Excel Workbook
	A
5	Group
6	BHO
7	BHO
8	BHO
9	BHO
10
11	Group
12	RIO
13	RIO
14	RIO
15
16	Group
17	CBA
18	CBA
19	CBA
20	CBA
21
Sheet1

 

[jeffreybrown]

Remove the . in front of Rows

 

[Emmily]

i get the same error on this

For i = .Cells

 

[xenou]

[COLOR="Navy"]Sub[/COLOR] foo()
    [COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] myWs [COLOR="Navy"]As[/COLOR] Worksheet
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] myWs [COLOR="Navy"]In[/COLOR] Sheets(Array("BHP", "RIO", "CBA"))
        [B][COLOR="Red"]With[/COLOR] [COLOR="Red"]myWs[/COLOR][/B]
            [COLOR="Navy"]For[/COLOR] i = .Cells(.Rows.Count, "A").End(xlUp).Row [COLOR="Navy"]To[/COLOR] 2 [COLOR="Navy"]Step[/COLOR] -1
                [COLOR="Navy"]If[/COLOR] .Cells(i, "A").Value = "Group" [COLOR="Navy"]And[/COLOR] LenB(.Cells(i, "A").Offset(1).Value) = 0 [COLOR="Navy"]Then[/COLOR]
                    .Cells(i, "A").Resize(2).EntireRow.Delete
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]Next[/COLOR] i
        [B][COLOR="Red"]End[/COLOR] [COLOR="Red"]With[/COLOR][/B]
    [COLOR="Navy"]Next[/COLOR] myWs

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Emmily]

Perfect, thanks