+ Reply to Thread
Results 1 to 13 of 13

excel macro to remove specific columns and rows + remove duplicate

  1. #1
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    excel macro to remove specific columns and rows + remove duplicate

    Hello,

    New forum user, I was a bit out of resources on google...so let's see if someone could maybe help me.

    I started something with a friend to sort our list and need your help on the below code, as is it not working like a charm I need you help.
    Actually we compiled several sources and try to make it work:

    PHP Code: 
    Sub GetFiles()
    Dim sThisFilePath          As String
       

    ''''''''''''''''''''''''''''''''find keywords and delete rows...'''''''''''''''''''''''

    '
    Workbooks(1).Activate
     mycount 
    Cells(Rows.Count1).End(xlUp).Row
    For 2 To mycount
    For 1 To 9
    If InStr(Cells(ij).Value"keyword 1") > 0 Then
    Cells
    (i1).EntireRow.Delete
    End 
    If
    If 
    InStr(Cells(ij).Value"keyword 2") > 0 Then
    Cells
    (i1).EntireRow.Delete
    End 
    If

    On Error Resume Next


    Next j
    Next i
    '''''''''''''''' new updates'''''''''''''''''''''''''''''''''

    mycount = Cells(Rows.Count, 1).End(xlUp).Row
    '
    MsgBox mycount
    For 2 To mycount
    9

    If InStr(Cells(i9).Value"(") > Or InStr(Cells(i9).Value")") > 0 Then
    text1 
    Cells(i9).Value
    InStr(text1"(")
    InStr(text1")")
    MsgBox r
    MsgBox s
    MsgBox text1
    text2 
    Mid(text111)
    MsgBox text2
    Cells
    (ij).Value text2
    End 
    If
    If 
    InStr(Cells(i9).Value"qq.com") > 0 Then
    Cells
    (i1).EntireRow.Delete
    End 
    If
    If 
    Len(Trim(Cells(i9).Value)) = 0 Then
    Cells
    (i1).EntireRow.Delete
    End 
    If
    If 
    InStr(Cells(i9).Value"@") = 0 Then
    Cells
    (i1).EntireRow.Delete
    End 
    If

    text1 Trim(Cells(i9).Value)
    If 
    Len(text1) < 4 Then
    Cells
    (i1).EntireRow.Delete
    End 
    If
    'If Left(Cells(i, 9).Value, 1) = "(" Then
    '
    Cells(i9).Value Mid(Cells(i9).Value2Len(Cells(i9).Value) - 2)
    'End If
    '
    If Left(Cells(i9).Value3) = "   " Then
    'Cells(i, 9).EntireRow.Delete
    '
    End If
    Cells(i9).Select
    If IsEmpty(ActiveCellThen
    Cells
    (i9).EntireRow.Delete
    End 
    If

    Next i
    ''''''''''''''''''''''''''''Remove duplicates '''''''''''''''''''''''''''''''''''''''''
    mycount = Cells(Rows.Count, 1).End(xlUp).Row
    '
    MsgBox mycount
    For 2 To mycount
    text1 
    Trim(Cells(i9).Value)
    For 
    1 To mycount
    If Trim(Cells(j9).Value) = text1 Then
    Cells
    (j9).EntireRow.Delete
    mycount 
    mycount 1
    End 
    If
    Next j
    Next i
     
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     

        


    '
    For 2 To sheetscount
    'Workbooks(i).Close
    '
    Next i
     
    End Sub 
    Any comment will be welcome!
    Thank you in advance

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: excel macro to remove specific columns and rows + remove duplicate

    You have to be careful when looping and deleting entire rows or columns because you'll keep losing your point of reference.

    Consider a program which is looping through the first three columns of the first three rows using two variables, RowLoop and ColLoop.

    Let's say you find your first match on row 1, column 2, i.e RowLoop=1, ColLoop=2, and you delete that entire row. That means that what was row 2 now becomes row 1, so when you increment ColLoop you're looking at what was originally row 2, column 3 - the values in columns 1 & 2 of the original row 2 will never be checked.

    It's also hideously slow to loop through every cell, you really want to use the .Find method to quickly find the values you're looking for.

  3. #3
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    Hello,

    Thanks for your reply.

    So you mean I have to rewrite the whole system as it is not optimal like that?

  4. #4
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    Can I correct it by adding i = i - 1
    after every entire row.delete line ?

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: excel macro to remove specific columns and rows + remove duplicate

    You can correct it by adding i=i-1 and restarting the column loop.

    This would be an easier and much, much faster way of doing it:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    Ok, tried it but I think as my file is a .csv it makes an error: http://screencast.com/t/cmL9k491Pa <<<<<Forget this!

    I tried and it seems working I just need to change the column as it it to J

    Const sSTART_CELL = "J2" -Right?
    And could it do "*keyword*" ?

    Again thanks for your precious help
    Last edited by garrywelson; 01-16-2013 at 10:32 AM.

  7. #7
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: excel macro to remove specific columns and rows + remove duplicate

    It looks like you've stopped another macro part way through before you tried to run this one.

    Go to the VB editor and press the stop button and then try running it.

  8. #8
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    I'm still a bit confuse with the code I can't compile it as I would like...

    PHP Code: 
    Sub GetFiles()
    Dim sThisFilePath 
    As String

       
    ''''''''''''''''''''''''''1Delete columns '''''''''''''''''''''''''''''''''''''''''


    Workbooks(1).Activate
    With Sheets("???active sheet????")

            
            .Columns("AB:AB").EntireColumn.delete
            .Columns("Y:Z").EntireColumn.delete
             .Columns("R:T").EntireColumn.delete
             .Columns("P:P").EntireColumn.delete
            .Columns("B:N").EntireColumn.delete
             .Columns("R:T").EntireColumn.delete
        End With

            End Sub

       

    ''''''''''''''''''''''''''''''''2) ANDREW _ R KEYWORD FINDER AND ROW REMOVE.'''''''''''''''''''''''
    Sub DeleteRows()

    Const 
    sSTART_CELL "A2"

    Dim rngSearchArea As Range
    Dim rngMatch 
    As Range
    Dim avSearchTerms 
    As Variant
    Dim lSearchLoop 
    As Long

    avSearchTerms 
    = Array("keyword 1""keyword 2")

    Set rngSearchArea Range(Range(sSTART_CELL), Cells(Rows.CountRange(sSTART_CELL).Column).End(xlUp)).Resize(, 9)

    For 
    lSearchLoop LBound(avSearchTermsTo UBound(avSearchTerms)

      
    Set rngMatch rngSearchArea.Find(avSearchTerms(lSearchLoop), LookIn:=xlValueslookat:=xlWholeMatchCase:=False)
      
      While 
    Not rngMatch Is Nothing
      
        rngMatch
    .EntireRow.Delete
            
        Set rngMatch 
    rngSearchArea.Find(avSearchTerms(lSearchLoop))
      
    Wend

    Next lSearchLoop
        

    End Sub
    '''''''''''''''' 3) new updates REMOVE ()before and after text'''''''''''''''''''''''''''''''''

    Cells.Select
    Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False 

    Next i
    ''''''''''''''''''''''''''''Remove duplicates '''''''''''''''''''''''''''''''''''''''''
    mycount Cells(Rows.Count1).End(xlUp).Row
    'MsgBox mycount
    For i = 2 To mycount
    text1 = Trim(Cells(i, 9).Value)
    For j = i + 1 To mycount
    If Trim(Cells(j, 9).Value) = text1 Then
    Cells(j, 9).EntireRow.Delete
    mycount = mycount - 1
    End If
    Next j
    Next i
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     

        
    'For i = 2 To sheetscount
    '
    Workbooks(i).Close
    'Next i
     
    End Sub  
    Next i
    ''''''''''''''''''''''''''''Remove duplicates '''''''''''''''''''''''''''''''''''''''''
    mycount Cells(Rows.Count1).End(xlUp).Row
    'MsgBox mycount
    For i = 2 To mycount
    text1 = Trim(Cells(i, 9).Value)
    For j = i + 1 To mycount
    If Trim(Cells(j, 9).Value) = text1 Then
    Cells(j, 9).EntireRow.Delete
    mycount = mycount - 1
    End If
    Next j
    Next i
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     

        


    'For i = 2 To sheetscount
    '
    Workbooks(i).Close
    'Next i
     
    End Sub 
    Last edited by garrywelson; 01-16-2013 at 11:36 AM.

  9. #9
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    I think this time code is correctly paste, even if messy

  10. #10
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: excel macro to remove specific columns and rows + remove duplicate

    Is it giving an error?

  11. #11
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    Yes I get this error: http://screencast.com/t/qPbVjLp1Fy

  12. #12
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: excel macro to remove specific columns and rows + remove duplicate

    OK, that's not part of my code, but seems to be at the end of the floating block of code which doesn't belong to any sub.

    I think you need to double check where you've pasted things in to VB.

  13. #13
    Registered User
    Join Date
    01-16-2013
    Location
    Swiss
    MS-Off Ver
    Office 2010
    Posts
    7

    Re: excel macro to remove specific columns and rows + remove duplicate

    Ok, so it wasn't working as I would like so I made 4 VB and call them with a 5th one....so we can close this topic

+ 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