Very small macro/VBA stuff, deleting rows.
 

What I want to do is delete the row that has the same value in
specific collumn (collumn 7 or G) as the row before it. Continue doin
this till the values are different. I have no experience whatsoever i
VBA or macros, but have programmed in C and Java before. This is th
code I already came up with on my own;


Code
-------------------

Sub DeleteDoubles()
Worksheets("sheet1").Activate
Dim i
For i = 0 To ActiveSheet.Rows.Count
While (ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i + 1, 7).Value)
Range(Cells(i + 1, 1), Cells(i + 1, 15)).Delete (xlShiftUp)
Wend
Next i
End Sub

-------------------


I still get a compile error in the definition of the while loop, erro
1004 application defined or object defined error. It also won't compil
past the For part if I put Dim i As Integer, which seems strange. Wha
is still wrong with it

--
Message posted from http://www.ExcelForum.com

Very small macro/VBA stuff, deleting rows.
 

Hello!

Just 2 quick remarks, since I ll go home soon :)

1)
There is no row 0
Row numbers of enumerations of rows start at 1

2)
If I am not mistakened ActiveSheet.Rows.Count will always return 65536
You could use something like ActiveSheet.UsedRange.Rows.Count +
ActiveSheet.UsedRange.Row -1

(= number of used rows plus offset minus one)

Also keep in mind that when deleting rows in a loop the number of row
will derease with every deletion.

Ro

--
Message posted from http://www.ExcelForum.com

Very small macro/VBA stuff, deleting rows.
 

Hi,
I recommend For ... Next structure begining with last row and with ste
-1

Sub DeleteDoubles2()
Worksheets("sheet1").Activate
Dim i As Integer
Dim row_counter As Integer

row_counter = ActiveSheet.Rows.Count
For i = row_counter To 1 Step -1
If ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i - 1, 7).Valu
Then
Range(Cells(i, 1), Cells(i, 15)).Delete (xlShiftUp)
End If
Next i
End Sub

Jare

--
Message posted from http://www.ExcelForum.com

Very small macro/VBA stuff, deleting rows.
 

Sub DeleteDoubles()
Worksheets("sheet1").Activate
Dim i
For i = cells(.Rows.Count,7).End(xlup).row to 2 step -1
if ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i - 1, 7).Value) then
Range(Cells(i , 1), Cells(i , 15)).Delete xlShiftUp
Next i
End Sub

--
Regards,
Tom Ogilvy


"Sintel " wrote in message
...
What I want to do is delete the row that has the same value in a
specific collumn (collumn 7 or G) as the row before it. Continue doing
this till the values are different. I have no experience whatsoever in
VBA or macros, but have programmed in C and Java before. This is the
code I already came up with on my own;


Code:
--------------------

Sub DeleteDoubles()
Worksheets("sheet1").Activate
Dim i
For i = 0 To ActiveSheet.Rows.Count
While (ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i + 1,
7).Value)
Range(Cells(i + 1, 1), Cells(i + 1, 15)).Delete (xlShiftUp)
Wend
Next i
End Sub

--------------------


I still get a compile error in the definition of the while loop, error
1004 application defined or object defined error. It also won't compile
past the For part if I put Dim i As Integer, which seems strange. What
is still wrong with it?


---
Message posted from http://www.ExcelForum.com/

Very small macro/VBA stuff, deleting rows.
 

I have taken the three of your replies in account and this is what
ended up with.


Code
-------------------

Sub DeleteDoubles()
Worksheets("sheet1").Activate
Dim i As Integer, j As Integer
j = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
For i = j To 1 Step -1
If ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i - 1, 7).Value Then
Rows(i).Delete Shift:=xlUp
End If
Next i
End Sub

-------------------


I used the last row expression from Roderick, the backwards structur
from Jarek, but it still wouldn't compile, then I saw Tom's reply an
changed the 1 into a 2 and it worked! I checked your algorithm too to
and it works as well. I could probably do this without the two integer
but i'm not too concerned with efficiency right now ^^ thx a bunch all

--
Message posted from http://www.ExcelForum.com

Very small macro/VBA stuff, deleting rows.
 

Hi,
----------------
Sub DelRows()
Dim LastRw, i
Sheets("Sheet3").Activate
LastRw = Cells(Rows.Count, 7).End(xlUp).Row
For i = LastRw - 1 To 1 Step -1
If Cells(i, 7) = Cells(i + 1, 7) Then
Range(Cells(i + 1, 1), Cells(i + 1, 15)).Delete (xlShiftUp)
End If
Next
End Sub
-----------------
To delete the entire row use
Rows(i).Entirerow.Delete instead of
Range(Cells(i + 1, 1), Cells(i + 1, 15)).Delete (xlShiftUp)

Regards,
Don


"Sintel " wrote in message
...
What I want to do is delete the row that has the same value in a
specific collumn (collumn 7 or G) as the row before it. Continue doing
this till the values are different. I have no experience whatsoever in
VBA or macros, but have programmed in C and Java before. This is the
code I already came up with on my own;


Code:
--------------------

Sub DeleteDoubles()
Worksheets("sheet1").Activate
Dim i
For i = 0 To ActiveSheet.Rows.Count
While (ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i + 1,
7).Value)
Range(Cells(i + 1, 1), Cells(i + 1, 15)).Delete (xlShiftUp)
Wend
Next i
End Sub

--------------------


I still get a compile error in the definition of the while loop, error
1004 application defined or object defined error. It also won't compile
past the For part if I put Dim i As Integer, which seems strange. What
is still wrong with it?


---
Message posted from http://www.ExcelForum.com/

Very small macro/VBA stuff, deleting rows.
 

j = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

will either be equivalent to

cells(.Rows.Count,7).End(xlup).row

or it will start at a higher row number and delete rows between that row and
the first cell with a value in column 7. (since a blank cell will match a
blank cell)

What you use will depend on what you want to accomplish.

--
Regards,
Tom Ogilvy


"Sintel " wrote in message
...
I have taken the three of your replies in account and this is what I
ended up with.


Code:
--------------------

Sub DeleteDoubles()
Worksheets("sheet1").Activate
Dim i As Integer, j As Integer
j = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
For i = j To 1 Step -1
If ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(i - 1, 7).Value
Then
Rows(i).Delete Shift:=xlUp
End If
Next i
End Sub

--------------------


I used the last row expression from Roderick, the backwards structure
from Jarek, but it still wouldn't compile, then I saw Tom's reply and
changed the 1 into a 2 and it worked! I checked your algorithm too tom
and it works as well. I could probably do this without the two integers
but i'm not too concerned with efficiency right now ^^ thx a bunch all.


---
Message posted from http://www.ExcelForum.com/