+ Reply to Thread
Results 1 to 3 of 3

how to delete data in two columns based on multiple criteria?

Hybrid View

marreco how to delete data in two... 06-21-2013, 07:51 PM
JapanDave Re: how to delete data in two... 06-21-2013, 08:58 PM
JasperD Re: how to delete data in two... 06-21-2013, 09:01 PM
  1. #1
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    how to delete data in two columns based on multiple criteria?

    Hi.
    how to delete data in two columns based on multiple criteria?
    Option Explicit
    Public Sub DeleteRows_Two_Columns()
       Dim iLastRow As Long
       Dim aLastRow As Long
       Dim i As Long
        iLastRow = Cells(Rows.Count, "T").End(xlUp).Row
        aLastRow = Cells(Rows.Count, "I").End(xlUp).Row
      For i = iLastRow To 2 Step -1
            Select Case Cells(i, "T").Value
                Case "CSAV LONCOMILLA"
                Case "CAP HARALD"
                Case "CAP ISABEL"
                Case "CAP HENRI"
                Case "CAP IRENE"
                Case "CAP IRENE"
                Case "CSAV LAJA"
                Case "CAP JERVIS"
                Case "CSAV JERVIS"
                Case "CSAV LONCOMILLA"
                Case "LIMARI"
                Case "CSAV LIMARI"
                Case Else
                    Rows(i).Delete
                End Select
        Next i
          
    For y = LastRow To 2 Step -1
    If Cells(y, "I").Value = "SOROCABA" Then Rows(y).EntireRow.Delete
    Next y
         
    End Sub
    "No xadrez nem sempre a menor dist?ncia entre dois pontos ? uma linha reta" G. Kasparov.

    If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select b from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: how to delete data in two columns based on multiple criteria?

    Try this,
    Public Sub DeleteRows_Two_Columns()
       Dim iLastRow As Long
       Dim aLastRow As Long
       Dim i As Long
        iLastRow = Cells(Rows.Count, "T").End(xlUp).Row
        aLastRow = Cells(Rows.Count, "I").End(xlUp).Row
       For i = iLastRow To 2 Step -1
            If Cells(i, "T").Value <> "CSAV LONCOMILLA" And Cells(i, "T").Value <> "CAP HARALD" And Cells(i, "T").Value <> "CAP ISABEL" And Cells(i, "T").Value <> "CAP HENRI" _
                And Cells(i, "T").Value <> "CAP IRENE" And Cells(i, "T").Value <> "CSAV LAJA" And Cells(i, "T").Value <> "CAP JERVIS" And Cells(i, "T").Value <> "CSAV JERVIS" _
                 And Cells(i, "T").Value <> "LIMARI" And Cells(i, "T").Value <> "CSAV LIMARI" Then
                    Rows(i).Delete
            End If
      Next i
          
    For y = LastRow To 2 Step -1
    If Cells(y, "I").Value = "SOROCABA" Then Rows(y).EntireRow.Delete
    Next y
         
    End Sub
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: how to delete data in two columns based on multiple criteria?

    Option Explicit
    Public Sub DeleteRows_Two_Columns()
       Dim iLastRow&, i&
        iLastRow = Cells(Rows.Count, "T").End(xlUp).Row
      For i = iLastRow To 2 Step -1
            Select Case Cells(i, "T").Value
                Case "CSAV LONCOMILLA"
                Case "CAP HARALD"
                Case "CAP ISABEL"
                Case "CAP HENRI"
                Case "CAP IRENE"
                Case "CAP IRENE"
                Case "CSAV LAJA"
                Case "CAP JERVIS"
                Case "CSAV JERVIS"
                Case "CSAV LONCOMILLA"
                Case "LIMARI"
                Case "CSAV LIMARI"
                Case Else
                    Rows(i).Delete
                End Select
    If Cells(i, "I").Value = "SOROCABA" Then Rows(i).EntireRow.Delete
    Next i
    End Sub
    Please click the * below if this helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1