Need to delete a group of rows based on criteria

[SimondsJM]

I have a sheet with data on it that sometimes needs to be removed.

What I have is a form that you can select the item in column B to be removed but i also need data in column C to be removed below that.


---------------------
Column B|Column C|
Data in B| No Data|
No Data | Data 1
No Data | Data 2
No Data | Data 3
---------------------
New Data| No Data
No Data | Data 4
No Data | Data 5

So If I select "Data in B" from my combobox on the form I want all coresponding data (Data 1-5) to be removed as well. The areas labeled No Data are blank.

Any help is greatly appreciated.

 

[JuanPa]

So, you have a form with a combo.
This combo shows all the content from column B, that should come from something like this:

[FONT=Courier New][SIZE=2][COLOR=Navy]Option Explicit[/COLOR]
[COLOR=Navy]Private Sub [/COLOR]UserForm_Activate()
    [COLOR=Navy]With [/COLOR]Me.ComboBox1
        .Clear
        .RowSource = Range("B1:B" & Cells(1, 2).End(xlDown).Row).Address
    [COLOR=Navy]End With[/COLOR]
[COLOR=Navy]End Sub[/COLOR][/SIZE][/FONT]

Then, you need use a button in order to delete the rows, which shoulddo something like this:

[FONT=Courier New][SIZE=2][COLOR=Navy]Private [/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2]Sub CommandButton1_Click()
    [/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=Navy]With [/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2]Me.ComboBox1
        [/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=Navy]If [/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2].ListIndex <> -1 [/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=Navy]Then[/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2]
            Range(.RowSource).Rows(.ListIndex + 1 & ":" & .ListCount).Delete
        [/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=Navy]End If[/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2]
    [/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=Navy]End With[/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=Navy]End Sub[/COLOR][/SIZE][/FONT]

Does it makes sense to you?

JuanPa

 

[SimondsJM]

It kinda makes sense.

This is what I have to populate my combobox on the form.

Private Sub UserForm_Initialize()
    Dim c As Range
    With Sheets("Vendor List")
        For Each c In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then CBox1.AddItem c.Value
        Next c
    End With
End Sub

To help explain the data:
In column B is a vendors name
In column C is the vendors contact info filled in beside vendor name
In column D is the classes offered by vendor which is skipped down a row and columns B & C are blank to the left.

I need to remove Columns B,C, & D that correspond to that vendor to include all the classes listed

Hope this helps

 

[JuanPa]

Looks tricky. Why don't you save the row number in a hidden column of the combo?
Replace:

[FONT=Courier New][COLOR=Navy]If [/COLOR]c.Value <> "" [COLOR=Navy]Then [/COLOR]CBox1.AddItem c.Value[/FONT]

By:

[COLOR=Navy]If [/COLOR]c.Value <> "" [COLOR=Navy]Then[/COLOR]
    CBox1.AddItem
    CBox.List(CBox.ListCount-1, 0)=.Value
    CBox.List(CBox.ListCount-1, 1)=.Row
[COLOR=Navy]End If[/COLOR]

This way, you already know where is the record located.
Just make sure the combo's properties ColumnCount and ColumnWidths are set to 2 and "100 pt;0 pt", respectively.

Then, let the button use it to determine the cells to delete:

[COLOR=Navy]Private Sub [/COLOR]CommandButton1_Click()
    [COLOR=Navy]If [/COLOR]Me.CBox1.ListIndex <> -1 [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]Dim [/COLOR]r [COLOR=Navy]As Integer
[/COLOR]        r = Me.CBox1.List(.ListIndex, 1)
        [COLOR=Navy]With [/COLOR]Sheets("Vendor List")
            .Rows( r & ":" & .Cells(r, 2).End(xlDown).Row - 1).Delete
        [COLOR=Navy]End With[/COLOR]
    [COLOR=Navy]End If[/COLOR]
[COLOR=Navy]End Sub[/COLOR]

What about now?