Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA in Excel, need help with a basic function (Excel XP/2002)

    Hello all,
    Here's my situation, I've got an Excel spreadsheet with 8 columns and about 1500 rows. Column G has about 5 different values (let's say Value1 - Value5) it could be and I only want Value1 - Value3. How do I loop through column G and if the value is Value4 or Value5, delete the entire row? I know this can be done using VBA but I'm not sure exactly how to do it, being that I'm not good with VBA. Thanks!

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel, need help with a basic function (Excel XP/2002)

    Do you need VBA? What about selecting the whole range, sort on Column G, select the rows with the unwanted values and deleting them manually.

    Unless you have to do it often, this may be better, because you can actually inspect the deletions first.
    Ruth

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel, need help with a basic function

    If you want to do it with VBA, then try the following. Of course, you will have to substitute whatever your Value4 and Value5 really are.

    <pre>Public Sub Del45()
    Dim lLast As Long, I As Long
    lLast = Worksheets("Sheet1").Range("G65535").End(xlUp).Row - 1
    For I = lLast To 0 Step -1
    With Worksheets("Sheet1").Range("G1").Offset(I, 0)
    If .Value = 4 Or .Value = 5 Then
    .EntireRow.Delete
    End If
    End With
    Next I
    End Sub
    </pre>

    Legare Coleman

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: VBA in Excel, need help with a basic function (Excel XP/2002)

    You can use Advanced filter to paste a copy of the filtered data into another part of the spreadsheet. This gives you the data you want without having to delete rows, and also leaves the original data intact.
    HTH <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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