Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Delete an entire row if two cells...... | Excel Discussion (Misc queries) | |||
Delete entire row if | Excel Discussion (Misc queries) | |||
Macro - delete entire row which contain a specific text | Excel Worksheet Functions | |||
CANNOT DELETE AN ENTIRE COLUMN | Excel Discussion (Misc queries) | |||
Can I delete an entire row if condition is not met? | Excel Worksheet Functions |