Results 1 to 3 of 3

Thread: Delete rows VBA

  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The enclosed spreadsheet has suppliers in column D. I have code that assigns, say, "TOFS" to the variable msgin. I want the code to find if the text "TOFS" occurs in the cell in column D and if it doesn't, delete the row. The range 'table1' contains all the spreadsheet.
    My current code is as follows but all bar one row ends up being deleted - what am I doing wrong?
    Thanks in advance!

    Sub Customer()
    Dim msgin
    Dim lngrow As Long
    Dim lngMaxRow As Long

    msgin = "TOF"

    ' Set up the range
    lngMaxRow = Range("a65536").End(xlUp).Row
    For lngrow = lngMaxRow To 2 Step -1 'Only go up to row 2
    Set ocell = ActiveSheet.Range("table1").Cells(lngrow, 5)
    If InStr(msgin, ocell.Value) = 0 Then
    ocell.EntireRow.Delete
    End If
    Next lngrow
    End Sub
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, it should read column E not column D - "suppliers in column E. I have code that assigns, say, "TOFS" to the variable msgin. I want the code to find if the text "TOFS" occurs in the cell in column E and if it doesn't, ..."
    Apologies.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your InStr has the variables the wrong way round. Try this version:
    Code:
    Sub Customer()
        Dim fname, fpath, savie, fnamepath, elkie, crit, resp, msgin
        Dim lngrow As Long
        Dim lngMaxRow As Long
        Dim myrange As Range
        Dim mylist
        Dim sfind, sdate As Long, edate As Long
    
        msgin = "TOF"
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        End With
        ' Set up the range
        lngMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
        For lngrow = lngMaxRow To 1 Step -1 'Only go up to row 2
            Set ocell = ActiveSheet.Range("table1").Cells(lngrow, 5)
            If InStr(ocell.Value, msgin) = 0 Then
                ocell.EntireRow.Delete
            End If
        Next lngrow
        With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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