Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 29th 09, 05:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 104
Default Macro to delete rows if...

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.


  #2   Report Post  
Old June 29th 09, 05:39 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 120
Default Macro to delete rows if...

Wouldn't Auto Filter does the job by deleting the rows after fitering for 0
on both columns?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Sasikiran" wrote:

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.

  #3   Report Post  
Old June 29th 09, 05:43 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Macro to delete rows if...

Hi Sasikiran

The below will only delete if 0's are present in G and H. Note the macro
will not delete blank rows....Try and feedback

Sub DeleteRows()
For lngRow = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1
If Range("G" & lngRow).Text = "0" And Range("H" & lngRow).Text = "0" Then
Rows(lngRow).Delete
End If
Next
End Sub

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


"Sasikiran" wrote:

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.

  #4   Report Post  
Old June 29th 09, 06:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 120
Default Macro to delete rows if...

Assuming that your data start from row 2 with header in row 1,
this code will delete rows if only both column G and H have a value of 0
if either G or H have a value other than 0, it will remain in the sheet.
try this in a module

Sub RemoveZero()
Dim r As Long
For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1
If Cells(r, 7).Value = 0 And _
Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete
Next r
End Sub

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Sasikiran" wrote:

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.

  #5   Report Post  
Old June 29th 09, 06:45 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 104
Default Macro to delete rows if...

Thank you so much...

"Jacob Skaria" wrote:

Hi Sasikiran

The below will only delete if 0's are present in G and H. Note the macro
will not delete blank rows....Try and feedback

Sub DeleteRows()
For lngRow = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1
If Range("G" & lngRow).Text = "0" And Range("H" & lngRow).Text = "0" Then
Rows(lngRow).Delete
End If
Next
End Sub

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


"Sasikiran" wrote:

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.



  #6   Report Post  
Old June 29th 09, 06:45 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 104
Default Macro to delete rows if...

Thank you so much Francis for your inputs.. It's working

"Francis" wrote:

Assuming that your data start from row 2 with header in row 1,
this code will delete rows if only both column G and H have a value of 0
if either G or H have a value other than 0, it will remain in the sheet.
try this in a module

Sub RemoveZero()
Dim r As Long
For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1
If Cells(r, 7).Value = 0 And _
Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete
Next r
End Sub

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Sasikiran" wrote:

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.

  #7   Report Post  
Old June 29th 09, 07:16 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 120
Default Macro to delete rows if...

Hi Sasikiran
Thanks for the feedback, glad I am of assistance.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Sasikiran" wrote:

Thank you so much Francis for your inputs.. It's working

"Francis" wrote:

Assuming that your data start from row 2 with header in row 1,
this code will delete rows if only both column G and H have a value of 0
if either G or H have a value other than 0, it will remain in the sheet.
try this in a module

Sub RemoveZero()
Dim r As Long
For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1
If Cells(r, 7).Value = 0 And _
Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete
Next r
End Sub

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Sasikiran" wrote:

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Delete rows using a macro Sasikiran Excel Discussion (Misc queries) 2 June 3rd 09 11:42 AM
macro to delete rows Aceensor Excel Discussion (Misc queries) 5 November 14th 08 04:45 PM
Need a macro to delete rows jmr4h8 Excel Discussion (Misc queries) 9 July 2nd 08 11:16 PM
Macro to Delete Certain Rows HROBERTSON Excel Discussion (Misc queries) 2 February 8th 07 10:42 PM
delete rows using macro nospam Excel Worksheet Functions 5 December 20th 06 02:26 PM


All times are GMT +1. The time now is 10:18 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017