Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
I would like to know how I would build a macro that deletes an entire row
that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
Give this macro a try...
Sub HideRowIfZeroInA() Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For Each R In .Range("A3:A" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet1 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
Take a look at the following website:
http://www.mvps.org/dmcritchie/excel/delempty.htm Go down until you see the information for: Delete ALL rows that have cell in Column A that looks blank This is a very mildly altered version from David's site to delete rows in column A that contain a ZERO: Sub DeleteRowsThatLookEmptyinColA() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim Rng As Range, ix As Long Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange) For ix = Rng.Count To 1 Step -1 If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "0" Then Rng.Item(ix).EntireRow.Delete End If Next done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Mark "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
Sorry, I grabbed a previous response to a similar question (only it wanted
to hide, not delete, the rows) and did a terrible job of modifying it for your question. Here is the code I should have posted... Sub DeleteRowIfZeroInA() Dim X As Long Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = LastRow To 1 Step -1 If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value < "" Then .Cells(X, "A").EntireRow.Delete xlShiftUp End If Next End With End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this macro a try... Sub HideRowIfZeroInA() Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For Each R In .Range("A3:A" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet1 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
Thanks Rick. Would I be able to reference more than 1 sheet in this macro i.e
(Sheet1, Sheet2 etc)? Thanks. -- Bob "Rick Rothstein (MVP - VB)" wrote: Sorry, I grabbed a previous response to a similar question (only it wanted to hide, not delete, the rows) and did a terrible job of modifying it for your question. Here is the code I should have posted... Sub DeleteRowIfZeroInA() Dim X As Long Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = LastRow To 1 Step -1 If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value < "" Then .Cells(X, "A").EntireRow.Delete xlShiftUp End If Next End With End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this macro a try... Sub HideRowIfZeroInA() Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For Each R In .Range("A3:A" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet1 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
hi, guys !
Bob wrote in message ... Thanks Rick. Would I be able to reference more than 1 sheet in this macro i.e (Sheet1, Sheet2 etc)? you might want to give a try to a differente approach using autofilter allows to delete rows in a single step and assuming row1 [A1] has a title (i.e.) Sub DeleteRowIfZeroInA_v2() Dim WS As Worksheet For Each WS In Worksheets(Array("sheet1", "sheet2", "sheet 5")) With WS.Range(WS.[a1], WS.[a65536].End(xlUp)) If Application.CountIf(.Offset(), 0) Then .AutoFilter 1, 0 .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete .AutoFilter End If End With Next End Sub hth, hector. Rick Rothstein wote in message ... Sorry, I grabbed a previous response to a similar question (only it wanted to hide, not delete, the rows) and did a terrible job of modifying it for your question. Here is the code I should have posted... Sub DeleteRowIfZeroInA() Dim X As Long Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = LastRow To 1 Step -1 If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value < "" Then .Cells(X, "A").EntireRow.Delete xlShiftUp End If Next End With End Sub Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
Thanks - the loop macro worked fine.
-- Bob "Mark Ivey" wrote: Take a look at the following website: http://www.mvps.org/dmcritchie/excel/delempty.htm Go down until you see the information for: Delete ALL rows that have cell in Column A that looks blank This is a very mildly altered version from David's site to delete rows in column A that contain a ZERO: Sub DeleteRowsThatLookEmptyinColA() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim Rng As Range, ix As Long Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange) For ix = Rng.Count To 1 Step -1 If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "0" Then Rng.Item(ix).EntireRow.Delete End If Next done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Mark "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
Glad it worked out...
Please thank David McRitchie for his help as well. This macro came from his site. Mark "Bob" wrote in message ... Thanks - the loop macro worked fine. -- Bob "Mark Ivey" wrote: Take a look at the following website: http://www.mvps.org/dmcritchie/excel/delempty.htm Go down until you see the information for: Delete ALL rows that have cell in Column A that looks blank This is a very mildly altered version from David's site to delete rows in column A that contain a ZERO: Sub DeleteRowsThatLookEmptyinColA() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim Rng As Range, ix As Long Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange) For ix = Rng.Count To 1 Step -1 If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "0" Then Rng.Item(ix).EntireRow.Delete End If Next done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Mark "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row that has a zero in column A
.Cells(X, "A").EntireRow.Delete xlShiftUp
Just to share a programming idea... When Excel deletes an entire row, it knows it must Shift the next row up. Rows(X).Delete -- HTH :) Dana DeLouis "Rick Rothstein (MVP - VB)" wrote in message ... Sorry, I grabbed a previous response to a similar question (only it wanted to hide, not delete, the rows) and did a terrible job of modifying it for your question. Here is the code I should have posted... Sub DeleteRowIfZeroInA() Dim X As Long Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = LastRow To 1 Step -1 If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value < "" Then .Cells(X, "A").EntireRow.Delete xlShiftUp End If Next End With End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this macro a try... Sub HideRowIfZeroInA() Dim R As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For Each R In .Range("A3:A" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet1 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Bob" wrote in message ... I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting every nth through every nth row in a column | Excel Discussion (Misc queries) | |||
Deleting cells in a column ref a different column | Excel Programming | |||
Deleting a row by a column value | Excel Programming | |||
Deleting a Column | Excel Programming | |||
Deleting a row with 0 in column A | Excel Programming |