Results 1 to 3 of 3

Thread: vba loop query

  1. #1
    Join Date
    Jul 2004
    Posts
    18

    Unanswered: vba loop query

    HI I wondered if anyone can help me on this query.

    I currently receive a download form our mainfram database which has about 32 columns in it. I can automatically download it in to access because of restriction however I do that afterward.

    I would like to build a function that goes down one of the columns and if it says company total it then deletes that whole row.

    Im nit sure how to do this?

    Can anyone help

    Thanks
    in advance

    Regards

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You coul try somethink like this

    Code:
    Sub DeleteCompanyTotalRows()
    
        Dim i As Long
        Dim LastRow As Long
        Dim YourColumn As Long
        
        Application.ScreenUpdating = False
        
        YourColumn = 3 ' column containing 'Company Total'
        
        Cells(65536, YourColumn).Select
        
        Selection.End(xlUp).Select
        LastRow = ActiveCell.Row
        
        Cells(1, YourColumn).Select
        i = 1
        Do Until i > LastRow
            'UCase() make this not case sensitive
            If InStr(UCase(Cells(i, YourColumn)), "COMPANY TOTAL") <> 0 Then
                Cells(i, YourColumn).EntireRow.Delete
                i = i - 1 'decriment as all rows have move up row
            End If
            i = i + 1
        Loop
        
        Application.ScreenUpdating = True
    End Sub
    HTH


    MTB

  3. #3
    Join Date
    Jul 2004
    Posts
    18
    Hi Mike

    Many thanks for your help. I will give this a try and see if it works.

    Thanks again for you help

    Regards

Posting Permissions

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