+ Reply to Thread
Results 1 to 5 of 5

VBA Question - Is there a better approach?

  1. #1
    Scott Wagner
    Guest

    VBA Question - Is there a better approach?

    Below is a piece of code I use to delete rows that contain a specific keyword
    in a designated column. Right now I specify the range m2:m1500, but rarely
    have that many lines in the raw data. I chose the number 1500 to be sure the
    code runs on all lines. Right now this is the largest time contributor to
    the macro I run on the data, of which this is a small part.

    Is there an approach I can follow other than the one shown below that
    addresses the speed, and would also not require me to specify the range... so
    it acts only the actual number of row of data in the worksheet?

    Thanks,

    Scott



    '----------------------------------------------------
    'Delete extra lines
    '----------------------------------------------------
    myWords = Array("2")

    Set wks = ActiveSheet
    With wks
    With .Range("m2:m1500")
    For iCtr = LBound(myWords) To UBound(myWords)
    Do
    Set FoundCell = .Cells.Find(What:=myWords(iCtr), _
    after:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    searchdirection:=xlNext, _
    MatchCase:=False)
    If FoundCell Is Nothing Then
    Exit Do
    Else
    FoundCell.EntireRow.Delete
    End If
    Loop
    Next iCtr
    End With
    End With

  2. #2
    Vasant Nanavati
    Guest

    Re: VBA Question - Is there a better approach?

    Instead of:

    ..Range("M2:M1500")

    try using:

    Intersect(.UsedRange, .Range("M2:M1500")



    "Scott Wagner" <ScottWagner@discussions.microsoft.com> wrote in message
    news:FF39F0FD-E737-47E0-BF1B-35FED555A4B3@microsoft.com...
    > Below is a piece of code I use to delete rows that contain a specific
    > keyword
    > in a designated column. Right now I specify the range m2:m1500, but
    > rarely
    > have that many lines in the raw data. I chose the number 1500 to be sure
    > the
    > code runs on all lines. Right now this is the largest time contributor to
    > the macro I run on the data, of which this is a small part.
    >
    > Is there an approach I can follow other than the one shown below that
    > addresses the speed, and would also not require me to specify the range...
    > so
    > it acts only the actual number of row of data in the worksheet?
    >
    > Thanks,
    >
    > Scott
    >
    >
    >
    > '----------------------------------------------------
    > 'Delete extra lines
    > '----------------------------------------------------
    > myWords = Array("2")
    >
    > Set wks = ActiveSheet
    > With wks
    > With .Range("m2:m1500")
    > For iCtr = LBound(myWords) To UBound(myWords)
    > Do
    > Set FoundCell = .Cells.Find(What:=myWords(iCtr), _
    > after:=.Cells(.Cells.Count), _
    > LookIn:=xlValues, _
    > LookAt:=xlPart, _
    > SearchOrder:=xlByRows, _
    > searchdirection:=xlNext, _
    > MatchCase:=False)
    > If FoundCell Is Nothing Then
    > Exit Do
    > Else
    > FoundCell.EntireRow.Delete
    > End If
    > Loop
    > Next iCtr
    > End With
    > End With




  3. #3
    Rick Hansen
    Guest

    Re: VBA Question - Is there a better approach?

    Hi Scott, there is several ways to determine bottom row. See the example
    below.
    Enjoy Rick

    dim LastRow as Long
    LastRow = Range("M2").End(xlDn).Row
    With Range("M2:M" & LastRow
    (your code)

    or

    With Range("M2: M" & Range("M2").End(xlDn).Row )
    (your code)


    "Scott Wagner" <ScottWagner@discussions.microsoft.com> wrote in message
    news:FF39F0FD-E737-47E0-BF1B-35FED555A4B3@microsoft.com...
    > Below is a piece of code I use to delete rows that contain a specific

    keyword
    > in a designated column. Right now I specify the range m2:m1500, but

    rarely
    > have that many lines in the raw data. I chose the number 1500 to be sure

    the
    > code runs on all lines. Right now this is the largest time contributor to
    > the macro I run on the data, of which this is a small part.
    >
    > Is there an approach I can follow other than the one shown below that
    > addresses the speed, and would also not require me to specify the range...

    so
    > it acts only the actual number of row of data in the worksheet?
    >
    > Thanks,
    >
    > Scott
    >
    >
    >
    > '----------------------------------------------------
    > 'Delete extra lines
    > '----------------------------------------------------
    > myWords = Array("2")
    >
    > Set wks = ActiveSheet
    > With wks
    > With .Range("m2:m1500")
    > For iCtr = LBound(myWords) To UBound(myWords)
    > Do
    > Set FoundCell = .Cells.Find(What:=myWords(iCtr), _
    > after:=.Cells(.Cells.Count), _
    > LookIn:=xlValues, _
    > LookAt:=xlPart, _
    > SearchOrder:=xlByRows, _
    > searchdirection:=xlNext, _
    > MatchCase:=False)
    > If FoundCell Is Nothing Then
    > Exit Do
    > Else
    > FoundCell.EntireRow.Delete
    > End If
    > Loop
    > Next iCtr
    > End With
    > End With




  4. #4
    Martin Krastev
    Guest

    RE: VBA Question - Is there a better approach?

    Dear Scott,

    Please see the following code:
    '----------------------------------------------------
    'Delete extra lines
    '----------------------------------------------------
    Dim myWords As Variant
    Dim wks As Worksheet ' Worksheet object
    Dim r As Integer ' Row Iterator
    Dim w As Variant ' word iterator among myWords
    Dim lastRow As Integer ' Variable to store the last row number

    myWords = Array("2") ' Assign values to search for

    Application.ScreenUpdating = False ' Disables screen redraw, which is
    very slow
    Set wks = ActiveSheet ' get reference to the active WorkSheet
    lastRow = wks.Columns("M:M").Find("*", wks.Range("M1"), , , ,
    xlPrevious).Row ' Find the last cell in the range, containing a value
    For r = lastRow To 2 Step -1 ' Iterate from the last to the first row -
    important to be backwards, because rows change after deletion
    If wks.Cells(r, 13) <> "" Then ' Do not check if cell is empty. Not
    necessary
    For Each w In myWords ' Iterate among all words
    If InStr(wks.Cells(r, 13).Value, w) > 0 Then 'Check if w is
    contained in the cell
    wks.Rows(r).Delete ' delete the row
    Exit For ' exit word loop - no need to check other
    words, since row is deleted
    End If
    Next w
    End If
    Next r

    Application.ScreenUpdating = True ' Enables screen redraw

    Please keep in mind that the UsedRange is not always correct in Excel. So
    you can find the last cell "looking" backwards as shown above.
    Moreover, if many rows are to be deleted, I believe that disabling screen
    updating might speed up your code considerably


    "Scott Wagner" написа:

    > Below is a piece of code I use to delete rows that contain a specific keyword
    > in a designated column. Right now I specify the range m2:m1500, but rarely
    > have that many lines in the raw data. I chose the number 1500 to be sure the
    > code runs on all lines. Right now this is the largest time contributor to
    > the macro I run on the data, of which this is a small part.
    >
    > Is there an approach I can follow other than the one shown below that
    > addresses the speed, and would also not require me to specify the range... so
    > it acts only the actual number of row of data in the worksheet?
    >
    > Thanks,
    >
    > Scott
    >
    >
    >
    > '----------------------------------------------------
    > 'Delete extra lines
    > '----------------------------------------------------
    > myWords = Array("2")
    >
    > Set wks = ActiveSheet
    > With wks
    > With .Range("m2:m1500")
    > For iCtr = LBound(myWords) To UBound(myWords)
    > Do
    > Set FoundCell = .Cells.Find(What:=myWords(iCtr), _
    > after:=.Cells(.Cells.Count), _
    > LookIn:=xlValues, _
    > LookAt:=xlPart, _
    > SearchOrder:=xlByRows, _
    > searchdirection:=xlNext, _
    > MatchCase:=False)
    > If FoundCell Is Nothing Then
    > Exit Do
    > Else
    > FoundCell.EntireRow.Delete
    > End If
    > Loop
    > Next iCtr
    > End With
    > End With


  5. #5
    Jim Thomlinson
    Guest

    RE: VBA Question - Is there a better approach?

    Since you are using a find it will not make a substantial difference. In fact
    I would be inclined to use

    With .Columns("M")

    Then you do not have to worry about how many cells are populated. You are
    not iterating through each of the cells with a find...

    If you want to get a performance improvement change the code to accumulate
    all of the found ranges into one big range using the union operator and then
    just do one big delete at the end. Deleting one row at a time is a
    substantial drain on your resources.
    --
    HTH...

    Jim Thomlinson


    "Scott Wagner" wrote:

    > Below is a piece of code I use to delete rows that contain a specific keyword
    > in a designated column. Right now I specify the range m2:m1500, but rarely
    > have that many lines in the raw data. I chose the number 1500 to be sure the
    > code runs on all lines. Right now this is the largest time contributor to
    > the macro I run on the data, of which this is a small part.
    >
    > Is there an approach I can follow other than the one shown below that
    > addresses the speed, and would also not require me to specify the range... so
    > it acts only the actual number of row of data in the worksheet?
    >
    > Thanks,
    >
    > Scott
    >
    >
    >
    > '----------------------------------------------------
    > 'Delete extra lines
    > '----------------------------------------------------
    > myWords = Array("2")
    >
    > Set wks = ActiveSheet
    > With wks
    > With .Range("m2:m1500")
    > For iCtr = LBound(myWords) To UBound(myWords)
    > Do
    > Set FoundCell = .Cells.Find(What:=myWords(iCtr), _
    > after:=.Cells(.Cells.Count), _
    > LookIn:=xlValues, _
    > LookAt:=xlPart, _
    > SearchOrder:=xlByRows, _
    > searchdirection:=xlNext, _
    > MatchCase:=False)
    > If FoundCell Is Nothing Then
    > Exit Do
    > Else
    > FoundCell.EntireRow.Delete
    > End If
    > Loop
    > Next iCtr
    > End With
    > End With


+ 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