Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    writing a macro (excel 2000)

    In the following I am trying to delete an entire row within a macro if the next row equal the content of the previous row:

    Do While Range("A2").Offset(num, 0).EntireRow = Range("A2").Offset(num + 1, 0).EntireRow

    Range("A2").Offset(num + 1, 0).EntireRow.Delete

    Loop

    However, when runing it I get an error message (run-time error '13' - type mismatch) - what am I doing wrong here? I think in my do while statment I can't have the word "EntireRow" by itself without a proceeding argument. How do I write this part of the macro to do what I want it to do?

    Thanks
    LaMont

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

    Re: writing a macro (excel 2000)

    You can't compare entire rows like this. You would have to write a custom function that loops through the relevant cells, and returns True if all compared cells are equal, False otherwise:

    Function CompareRows(Row1 As Long, Row2 As Long, _
    Optional MaxCol As Long = 256) As Boolean
    Dim lngCol As Long
    For lngCol = 1 To MaxCol
    If Cells(Row1, lngCol) <> Cells(Row2, lngCol) Then Exit Function
    Next lngCol
    ' We only get here if no differences were found
    CompareRows = True
    End Function

    Use:

    Do While CompareRows(Num + 2, Num + 3) = True

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: writing a macro (excel 2000)

    It isn't valid syntax for comparing the contents of an entire row; as written you are comparing two objects, not values. Are you really comparing the contents of an entire row or are you comparing from just one or two columns? It might help if you post a bit more of the code, for example, it's not clear where "num" comes from; the posted code doesn't show what the value of "num" will be.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Garland, Texas, USA
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing a macro (excel 2000)

    Can you guys help me out with a macro? I know nothing about writing the code. See post 345004.

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing a macro (excel 2000)

    Hans,

    Does this macro also address the problem I mentioned in the reply to John? if so, how do I use it in the macro I copied in my reply message. Look at the attachment if your want a better understanding of what I'm trying to do.

    Thanks

  6. #6
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing a macro (excel 2000)

    The whole code is as follows:

    Sub Macro3()

    num = 0

    Do While Range("A2").Offset(num, 0) <> ""

    Do While Range("A2").Offset(num, 0).EntireRow = Range("A2").Offset(num + 1, 0).EntireRow
    Range("A2").Offset(num + 1, 0).EntireRow.Delete
    Loop

    num = num + 1

    Loop

    End Sub

    I am only comparing 4 columns so instead of writing EntireRow I tried another statement in the while do loop that reads the following:

    Do While Range("A2").Offset(num, 0) = Range("A2").Offset(num + 1, 0) & Range("B2").Offset(num, 0) = Range("B2").Offset(num + 1, 0) & etc. for columns C and D

    However, I am using characters in each cell and I think I found that the macro only compares the amount of characters being used - not the difference of characters from one cell compared to the next. So the macro isn't working right. Most rows has the same amount of characters, others rows has less of more. How do I get around this? Look at the attachment for an example of the data.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: writing a macro (excel 2000)

    <P ID="edit" class=small>(Edited by JohnBF on 18-Feb-04 09:34. Added 'End Function' as last line.
    )</P>Here's some crude code; it requires that you preselect the area to be operated on. I'm siuddenly pressed for time; can you please test this carefully against some limited sample data to make sure it works correctly? Perhaps HansV can clean it up.

    Sub delduprows()
    Dim lngRow1 As Long, lngRow2 As Long, lngLastRow As Long, lngC As Long
    Dim intMaxCol As Integer
    intMaxCol = Intersect(Selection, ActiveSheet.UsedRange).Columns.Count _
    + Selection.Column
    lngLastRow = Selection.rows.Count + Selection.Row - 1
    For lngC = lngLastRow To 1 Step -1
    lngRow1 = Cells(lngC, 1).Row
    lngRow2 = Cells(lngC + 1, 1).Row
    If CompareRows(lngRow1, lngRow2, intMaxCol) Then _
    Cells(lngRow2, 1).EntireRow.Delete
    Next lngC
    End Sub

    Function CompareRows(Row1 As Long, Row2 As Long, _
    Optional MaxCol As Integer = 256) As Boolean
    Dim lngCol As Long
    For lngCol = 1 To MaxCol
    If Cells(Row1, lngCol).Value <> Cells(Row2, lngCol).Value Then
    CompareRows = False
    Exit Function
    End If
    Next lngCol
    ' We only get here if no differences were found
    CompareRows = True
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: writing a macro (excel 2000)

    The code should work, provided you sort the data first - in your sample spreadsheet, there are identical non-adjacent rows. The code will not detect these as duplicates.

    Sub Macro3()
    Num = 0
    Do While Cells(Num + 2, 1) <> ""
    ' Compare first 4 columns
    Do While CompareRows(Num + 2, Num + 3, 4) = True
    Cells(Num + 3, 1).EntireRow.Delete
    Loop
    Num = Num + 1
    Loop
    End Sub

    Function CompareRows(Row1 As Long, Row2 As Long, _
    Optional MaxCol As Long = 256) As Boolean
    Dim lngCol As Long
    For lngCol = 1 To MaxCol
    If Cells(Row1, lngCol) <> Cells(Row2, lngCol) Then Exit Function
    Next lngCol
    ' We only get here if no differences were found
    CompareRows = True
    End Function

Posting Permissions

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