Delete Entire row?

[antman123]

I have found used bits of code i dont really understand on this one. I would like to make it so VBA will search an entire column for a value (the value is stored in cell "A29" say. If it finds it, then it will delete the entire row. This is what im trying to use (but it will only delete the top row)

Columns("B:B").Select
Selection.Find(What:=Range("a29"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.EntireRow.Delete

Thanks

 

[WillR]

Try this

Option Explicit

Sub DeleteRowsBasedOnCriteria()
Dim varcrit As Variant
'Assumes the list has a heading.
With ActiveSheet
    varcrit = .Range("A29").Value
    If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
       .Range("A1").AutoFilter Field:=2, Criteria1:=varcrit
'where field = column no & Criteria1 is value to delete in col
       .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
End With
End Sub

 

[antman123]

Thanks. Sorry to sound like a total doofus, but what does .Range(A1) represent?

At the moment all it does is move the search value up row by row, until it reaches a row of existing data (i.e. not empty rows). Then nothing happens.

 

[antman123]

Can i not just do something like

ActiveCell = WorksheetFunction.VLookup("A29", B:B, 1)
ActiveCell.EntireRow.Delete

?

 

[HalfAce]

To make your own code work, you can try:

Columns("B:B").Select
Selection.Find(What:=Range("a29").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.EntireRow.Delete

A few points of interest,
1. As it's written, this will only find & act on the first found instance of the specified value... any subsequent instances will be ignored.
2. Check out the bit After:=ActiveCell
You can set this to start looking from the cell of your choice
3. The bit LookIn:=xlFormulas can be changed to LookIn:=xlConstants
4. The bit LookAt:=xlPart means if your value in A29 is "bone" it will find and act on "dogbone", or "trombone" (You can change that to xlWhole to make it require a full match.)
5. SearchOrder:=xlByRows can be changed to SearchOrder:=xlByColumns
6. MatchCase:=False can be changed to true to make it case sensitive

Hope this helps clear it up some,
Dan

 

[antman123]

Ok thanks! That seems to work. Would I be asking for a miricle if it was possible to instead of deleting an entire row, just delete columns A to E of that row? I dont mean delete as in empty the cell.. I mean remove the cells so the ones below them all move up one.

 

[WillR]

Sub DeleteRowsBasedOnCriteria()
Dim varcrit As Variant
'Assumes the list has a heading in row 1.
With ActiveSheet
    varcrit = .Range("A29").Value
    If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
        .Range("A1").AutoFilter Field:=2, Criteria1:=varcrit
'where field = column no & Criteria1 is value to delete in col
        .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).Cells.Select
        .AutoFilterMode = False
        Selection.Cells.Delete
        Application.CutCopyMode = False
End With
End Sub

The above code will do what you require. It takes the delete criteria from Range A29 cell, but you can change this if you require - I would have thought you would need to as the value in that cell will change as a result of the record deletion.

 

[HalfAce]

I haven't tried Will's code, but for the other to work and only delete columns A:E (& move the cells below up) you could simply replace this line:

ActiveCell.EntireRow.Delete

with this line:

ActiveCell.Offset(, -1).Resize(1, 5).Delete Shift:=xlUp

Hope it helps,
Dan