Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Row increment (VBA) (2003)

    I'm trying to write a For loop that will increment the row each interation and check a value of a cell in a specific column.
    I'm having some troubles...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row increment (VBA) (2003)

    You could adapt this "skeleton" code:

    Sub MyMacro()
    ' This is the column - modify as needed
    Const strCol = "F"

    Dim lngRow as Long
    Dim lngMaxRow As Long

    lngMaxRow = ActiveSheet.Range(strCol & 65536).End(xlUp).Row
    For lngRow = 1 To lngMaxRow
    If ActiveSheet.Range(strCol & lngRow) = 37 Then
    ' Do something
    MsgBox "Value found in row " & lngRow
    Exit Sub
    End If
    Next lngRow
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row increment (VBA) (2003)

    In that case, you should loop backwards, for if you delete a row while looping forwards, you are pulling the rug from under the code.

    For lngRow = lngMaxRow To 1 Step -1
    If ActiveSheet.Range(strCol & lngRow) = 37 Then
    ActiveSheet.Rows(lngRow).Delete
    End If
    Next lngRow

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row increment (VBA) (2003)

    Perfect! I was able to edit it to fit my needs, however I cant figure out how to delete the row when a specific value is found.
    I figured it out:
    Rows(lngRow & ":" & lngRow).Select

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row increment (VBA) (2003)

    Awesome - you're so fast on the response time! =)

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row increment (VBA) (2003)

    I'm trying to modify this to check for duplicates and delete BOTH duplicate rows. I added a string variable called holder which checks the next value in the loop.

    For lngRow = lngMaxRow To 1 Step -1
    If lngRow = 1 Then
    Exit Sub
    End If
    holder = ActiveSheet.Range(strCol & lngRow - 1)
    If ActiveSheet.Range(strCol & lngRow) = holder Then
    ActiveSheet.Rows(lngRow).Delete
    ActiveSheet.Rows(lngRow - 1).Delete
    End If
    Next lngRow

    I just wanted to post to see if there are any problems I could run into using this.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Row increment (VBA) (2003)

    This is more than likely what you are not looking for but I created an application in Excel once which required autonumbering when an item was written in column B

    In a hidden column(E) I listed 1,2,3,..., 500

    Then in Column A I wrote this function IF(ISBLANK(B1),"",E1) where E1 was the value 1

    I protected these columns with the following code

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, ActiveSheet.Range("A1:A500,E1:E500")) Is Nothing Then

    Application.EnableEvents = True

    ActiveSheet.Range("A1").Select
    End If

    End Sub


    just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row increment (VBA) (2003)

    Can there ever be triplicates or higher? If not, you could use the following:

    Sub MyMacro()
    ' This is the column - modify as needed
    Const strCol = "F"
    Dim holder

    Dim lngRow As Long
    Dim lngMaxRow As Long

    lngMaxRow = ActiveSheet.Range(strCol & 65536).End(xlUp).Row
    For lngRow = lngMaxRow - 1 To 1 Step -1
    holder = ActiveSheet.Range(strCol & lngRow + 1)
    If ActiveSheet.Range(strCol & lngRow) = holder Then
    ActiveSheet.Range(lngRow & ":" & (lngRow + 1)).Delete
    End If
    Next lngRow
    End Sub

    If there is a possibility of higher multiples, the code becomes more complicated. Or you could use Data | Filter | Advanced Filter.

Posting Permissions

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