Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with macro to delete rows
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with macro to delete rows
I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Help with macro to delete rows
I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with macro to delete rows
I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Help with macro to delete rows
..Range("Z2").Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"

You need to double quotes instead of 1

"mattg" wrote:

I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with macro to delete rows
Hi Joel,

The formula gets entered correctly and produces the correct "1" or zero but
then every row is deleted regardless if it has "1" or zero in Column Z.

"Joel" wrote:

.Range("Z2").Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"

You need to double quotes instead of 1

"mattg" wrote:

I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Help with macro to delete rows
the formulas were showing up as having data in the cells even through the
cells appeared to be empty.


Sub test1()
With ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.Range("Z" & 2).Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"
.Range("Z" & 2).Copy _
Destination:=.Range("Z" & 2 & ":Z" & lstRw)
.Columns("Z").Copy
.Columns("Z").PasteSpecial _
Paste:=xlPasteValues
Rows("2:" & lstRw).Sort _
key1:=.Range("Z2")
Set c = .Range("Z2:Z" & lstRw).Find(what:=0, _
LookIn:=xlValues, lookat:=xlWhole, _
SearchDirection:=xlPrevious)

If Not c Is Nothing Then
.Rows("2:" & c.Row).Delete
End If
End With
End Sub

"mattg" wrote:

Hi Joel,

The formula gets entered correctly and produces the correct "1" or zero but
then every row is deleted regardless if it has "1" or zero in Column Z.

"Joel" wrote:

.Range("Z2").Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"

You need to double quotes instead of 1

"mattg" wrote:

I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Help with macro to delete rows
the formulas were showing up as having data in the cells even through the
cells appeared to be empty.


Sub test1()
With ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.Range("Z" & 2).Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"
.Range("Z" & 2).Copy _
Destination:=.Range("Z" & 2 & ":Z" & lstRw)
.Columns("Z").Copy
.Columns("Z").PasteSpecial _
Paste:=xlPasteValues
Rows("2:" & lstRw).Sort _
key1:=.Range("Z2")
Set c = .Range("Z2:Z" & lstRw).Find(what:=0, _
LookIn:=xlValues, lookat:=xlWhole, _
SearchDirection:=xlPrevious)

If Not c Is Nothing Then
.Rows("2:" & c.Row).Delete
End If
End With
End Sub

"mattg" wrote:

Hi Joel,

The formula gets entered correctly and produces the correct "1" or zero but
then every row is deleted regardless if it has "1" or zero in Column Z.

"Joel" wrote:

.Range("Z2").Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"

You need to double quotes instead of 1

"mattg" wrote:

I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with macro to delete rows
Hi Joel,

The formula gets entered correctly and produces the correct "1" or zero but
then every row is deleted regardless if it has "1" or zero in Column Z.

"Joel" wrote:

.Range("Z2").Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"

You need to double quotes instead of 1

"mattg" wrote:

I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Help with macro to delete rows
Check the variable names lstRow OR lstRw

If this post helps click Yes
---------------
Jacob Skaria


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default Help with macro to delete rows
OPTION EXPLICIT
Sub delRws()
Dim lstRw As Long, i As Long
lstRw = Cells(Rows.Count, "d").End(xlUp).Row

For i = lstRw To 2 Step -1
If NOT (Cells(i, "d") = Cells(i - 1, "d") And Cells(i, "d") =
Cells(i + 1, "d") ) Then
Row(i).Delete
end if
Next
End Sub

"mattg" wrote in message
...
I'm tyring to delete rows based on these criteria: If the value of any
given
cell in column D does not match the value of the cell before it or the
cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need a macro to delete rows jmr4h8 Excel Discussion (Misc queries) 9 July 2nd 08 11:16 PM
MACRO TO DELETE ROWS K[_2_] Excel Programming 5 February 6th 08 02:42 AM
Macro to delete Rows 1, 3 and 10 Rashid Khan Excel Programming 2 June 28th 04 04:03 PM
macro to delete entire rows when column A is blank ...a quick macro vikram Excel Programming 4 May 3rd 04 08:45 PM


All times are GMT +1. The time now is 05:21 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement