| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| 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 | |||
Linear Mode
