writing a macro (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

(Edited by JohnBF on 18-Feb-04 09:34. Added ‘End Function’ as last line.
)

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

Reply | Quote

(Edited by JohnBF on 18-Feb-04 09:34. Added ‘End Function’ as last line.
)

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote