#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Delete an entire row

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Delete an entire row

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default Delete an entire row

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Delete an entire row

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Delete an entire row

Sub deleterows()
Const sZeroAs String = "0"
Dim rngColumnN As Range
Dim i As Long

Set rngColumnN = _
ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
'Work backwards from bottom to top when deleting rows
Application.ScreenUpdating = False
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Delete an entire row

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Delete an entire row

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Delete an entire row

sorry try this
Const sZero As String = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sZero Then
..Cells(i).EntireRow.Delete
End If
Next i
End With

"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default Delete an entire row

Hi,

I didn't check your code but I suggest turning on the recorder and doing the
steps suggested by TM and then going back and generalizing to your situation.
--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Delete an entire row

Technically, I just need to know what code I must enter in a macro to clear
an entire row when the value "0" is found in column "N".

"ShaneDevenshire" wrote:

Hi,

I didn't check your code but I suggest turning on the recorder and doing the
steps suggested by TM and then going back and generalizing to your situation.
--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Delete an entire row

very sorry this will work for sure
Sub delete()
Const sZero As String = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _

ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) = sZero Then
..Cells(i).EntireRow.delete
End If
Next i
End With
End Sub

"One-Leg" wrote:

Technically, I just need to know what code I must enter in a macro to clear
an entire row when the value "0" is found in column "N".

"ShaneDevenshire" wrote:

Hi,

I didn't check your code but I suggest turning on the recorder and doing the
steps suggested by TM and then going back and generalizing to your situation.
--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Delete an entire row

replace this in my last post
From: .Cells(i).EntireRow.delete
To: .Cells(i).EntireRow.clear

"One-Leg" wrote:

Technically, I just need to know what code I must enter in a macro to clear
an entire row when the value "0" is found in column "N".

"ShaneDevenshire" wrote:

Hi,

I didn't check your code but I suggest turning on the recorder and doing the
steps suggested by TM and then going back and generalizing to your situation.
--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 23
Default Delete an entire row

Not working...

"Mike" wrote:

replace this in my last post
From: .Cells(i).EntireRow.delete
To: .Cells(i).EntireRow.clear

"One-Leg" wrote:

Technically, I just need to know what code I must enter in a macro to clear
an entire row when the value "0" is found in column "N".

"ShaneDevenshire" wrote:

Hi,

I didn't check your code but I suggest turning on the recorder and doing the
steps suggested by TM and then going back and generalizing to your situation.
--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Delete an entire row

Whats not working............? I have tested this and where there is a zero
in column N it will delete that entire row. You need to give more detail then
Not Working....

Whats's not working ?
Sub deleteZeroRows()
Const sZero As String = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = ActiveSheet.Range(Cells(1, "N"), _
Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) = sZero Then
..Cells(i).EntireRow.delete
'.Cells(i).EntireRow.Clear
End If
Next i
End With
End Sub

"One-Leg" wrote:

Not working...

"Mike" wrote:

replace this in my last post
From: .Cells(i).EntireRow.delete
To: .Cells(i).EntireRow.clear

"One-Leg" wrote:

Technically, I just need to know what code I must enter in a macro to clear
an entire row when the value "0" is found in column "N".

"ShaneDevenshire" wrote:

Hi,

I didn't check your code but I suggest turning on the recorder and doing the
steps suggested by TM and then going back and generalizing to your situation.
--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I added the following to my Macro and it doesn't remove the rows where a "0"
is found in cell(s) in column "N".

===================================
Const sZeroAsString = "0"
Dim rngColumnN As Range
Dim i As Long
Set rngColumnN = _
ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
' Work backwards from bottom to top when deleting rows
With rngColumnN
For i = .Rows.Count To 2 Step -1
If .Cells(i) < sUSA Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
===================================

What am I doing wrong???


"Mike" wrote:

Replace this line in my other post
FROM:ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
TO:ActiveSheet.Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))


"One-Leg" wrote:

Thanks but I'm looking for a way to have this done via a Macro!!!

I already have a macro that copies some values from one place to another and
at one poitn in time, I need to clear every rows where "0" is found in column
"N".

"Teethless mama" wrote:

Auto Filter the "0" then select the filter range and Delete


"One-Leg" wrote:

CORRECTION:

I would like to search every cell in column "N" and if a zero is found, to
clear the cells "K", "L", "N" and "O" of that specific row.

"One-Leg" wrote:

Hello,

What can I add in a macro that will seach cells N2:N65536 and if one cell
has the value "0", to delete that entire row (where the "0" was found)???

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 an entire row if two cells...... Red2003XLT Excel Discussion (Misc queries) 3 April 30th 08 10:48 PM
Delete entire row if David T Excel Discussion (Misc queries) 2 December 6th 06 11:14 PM
Macro - delete entire row which contain a specific text Dileep Chandran Excel Worksheet Functions 1 December 6th 06 02:08 PM
CANNOT DELETE AN ENTIRE COLUMN ibeetb Excel Discussion (Misc queries) 4 June 23rd 06 03:55 AM
Can I delete an entire row if condition is not met? Christine Excel Worksheet Functions 8 May 4th 06 10:47 AM


All times are GMT +1. The time now is 12:44 PM.

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"