How to delete row based on cell value

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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  amp  1  Select      2 2  Insert     Range sCol  amp  2     temp      With ActiveSheet          usedrange             lLastRow    Cells SpecialCells xlCellTypeLastCell  Row         Set rng   Range sCol  amp  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

User · Answer

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   -    gt  0 Then  InStr returns an integer of the position  if above 0 - It contains the string             rng Delete         End If      Next rng  End Sub

User · Answer

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

User · Answer

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  lt   ThisWorkbook ActiveSheet Range  A1   CurrentRegion Rows Count      If InStr 1  ThisWorkbook ActiveSheet Cells i  1  Text   -   vbTextCompare   gt  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

[vba] How to delete row based on cell value

Examples related to vba

Examples related to excel