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

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "A").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>            <SPAN style="color:#00007F">If</SPAN> Cells(i, "A").Value = "Group" And LenB(Cells(i, "A").Offset(1).Value) = 0 <SPAN style="color:#00007F">Then</SPAN><br>                Cells(i, "A").Resize(2).EntireRow.Delete<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[Emmily]

Perfect, thanks

 

[Emmily]

Smitty, can the code be expanded to do the same in two other worksheets (Test2,Test3) so in total there are 3 worksheets.

See if this does what you want:

Sub foo()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value = "Group" And LenB(Cells(i, "A").Offset(1).Value) = 0 Then
Cells(i, "A").Resize(2).EntireRow.Delete
End If
Next i
End Sub


HTH,

 

[Smitty]

Just add another loop.

E.G.

For x = 1 to 3
  With Sheets("Test"&x)
     ' other code
  End with
Next x

 

[Emmily]

Test1,2 and 3 are just sheet examples, actual naming convention of sheets is "

"BHP", "RIO" and "CBA"

 

[jeffreybrown]

How about...

Sub Test2()
    Dim myWs As Worksheet
    For Each myWs In Sheets(Array("BHP", "RIO", "CBA"))
     ' other code
    Next myWs
End Sub

 

[Emmily]

Ok, i have this, but when i run the macro from another sheet which are not the sheets mentioned in the code then the code does nothing

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

 

[jeffreybrown]

It is most likely because you have to set your qualifiers by adding the .

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

 

[Emmily]

I get a compile error "Invalid or unqualified reference

For i = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1