Delete Entire row?

antman123

Board Regular
Joined
Jan 24, 2005
Messages
72
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
Try this

Code:
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

Board Regular
Joined
Jan 24, 2005
Messages
72
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

Board Regular
Joined
Jan 24, 2005
Messages
72
Can i not just do something like

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

?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

To make your own code work, you can try:
Code:
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

Board Regular
Joined
Jan 24, 2005
Messages
72
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.

:biggrin:
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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
 

Forum statistics

Threads
1,148,529
Messages
5,747,230
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top