Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Deleting rows with macro | Excel Worksheet Functions | |||
deleting rows macro | Excel Programming |