CODEWITHSUNDEEP
vbaexcel
sikas
sikas

Reputation: 5523

How to delete row based on cell value

I have a worksheet, I need to delete rows based on cell value ..

Cells to check are in Column A ..

If cell contains "-" .. Delete Row

I can't find a way to do this .. I open a workbook, copy all contents to another workbook, then delete entire rows and columns, but there are specific rows that has to be removed based on cell value.

Need Help Here.

UPDATE

Sample of Data I have

Sample

Upvotes: 19

Views: 271507

Answers (7)

Jorjo Peralta
Jorjo Peralta

Reputation: 75

If you're file isn't too big you can always sort by the column that has the - and once they're all together just highlight and delete. Then re-sort back to what you want.

Upvotes: 4

BeachBum68
BeachBum68

Reputation: 96

This is the autofilter macro you could base a function off of:

Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:="=*-*", Operator:=xlAnd
Selection.AutoFilter

I use this autofilter function to delete matching rows:

Public Sub FindDelete(sCol As String, vSearch As Variant)
'Simple find and Delete
Dim lLastRow As Integer
Dim rng As Range
Dim rngDelete As Range
    Range(sCol & 1).Select
    [2:2].Insert
    Range(sCol & 2) = "temp"
    With ActiveSheet
        .usedrange
            lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        Set rng = Range(sCol & 2, Cells(lLastRow, sCol))
            rng.AutoFilter Field:=1, Criteria1:=vSearch, Operator:=xlAnd
        Set rngDelete = rng.SpecialCells(xlCellTypeVisible)
            rng.AutoFilter
            rngDelete.EntireRow.Delete
        .usedrange
    End With
End Sub

call it like:

call FindDelete "A", "=*-*"

It's saved me a lot of work. Good luck!

Upvotes: 1

zaheer
zaheer

Reputation: 79

if you want to delete rows based on some specific cell value. let suppose we have a file containing 10000 rows, and a fields having value of NULL. and based on that null value want to delete all those rows and records.

here are some simple tip. First open up Find Replace dialog, and on Replace tab, make all those cell containing NULL values with Blank. then press F5 and select the Blank option, now right click on the active sheet, and select delete, then option for Entire row.

it will delete all those rows based on cell value of containing word NULL.

Upvotes: 5

Peter L.
Peter L.

Reputation: 7304

The screenshot was very helpful - the following code will do the job (assuming data is located in column A starting A1):

Sub RemoveRows()

Dim i As Long

i = 1

Do While i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count

    If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 1).Text, "-", vbTextCompare) > 0 Then
        ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
    Else
        i = i + 1
    End If

Loop

End Sub

Sample file is shared: https://www.dropbox.com/s/2vhq6vw7ov7ssya/RemoweDashRows.xlsm

Upvotes: 15

Francis Dean
Francis Dean

Reputation: 2476

You can loop through each the cells in your range and use the InStr function to check if a cell contains a string, in your case; a hyphen.

Sub DeleteRowsWithHyphen()

    Dim rng As Range

    For Each rng In Range("A2:A10") 'Range of values to loop through

        If InStr(1, rng.Value, "-") > 0 Then 'InStr returns an integer of the position, if above 0 - It contains the string
            rng.Delete
        End If

    Next rng

End Sub

Upvotes: 1

weir
weir

Reputation: 4771

You could copy down a formula like the following in a new column...

=IF(ISNUMBER(FIND("-",A1)),1,0)

... then sort on that column, highlight all the rows where the value is 1 and delete them.

Upvotes: 9

John Bustos
John Bustos

Reputation: 19574

The easiest way to do this would be to use a filter.

You can either filter for any cells in column A that don't have a "-" and copy / paste, or (my more preferred method) filter for all cells that do have a "-" and then select all and delete - Once you remove the filter, you're left with what you need.

Hope this helps.

Upvotes: 18

Related Questions