Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 15th 08, 10:10 PM posted to microsoft.public.excel.programming
Bob Bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 972
Default 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   Report Post  
Old March 15th 08, 10:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 1
Default 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   Report Post  
Old March 15th 08, 10:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 171
Default 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   Report Post  
Old March 15th 08, 10:44 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 1
Default 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   Report Post  
Old March 16th 08, 12:07 AM posted to microsoft.public.excel.programming
Bob Bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 972
Default 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   Report Post  
Old March 16th 08, 07:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 434
Default 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   Report Post  
Old March 16th 08, 03:23 PM posted to microsoft.public.excel.programming
Bob Bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 972
Default 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   Report Post  
Old March 16th 08, 05:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 171
Default 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   Report Post  
Old March 17th 08, 03:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 947
Default 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
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
Deleting every nth through every nth row in a column Chris Excel Discussion (Misc queries) 1 July 9th 06 09:23 AM
Deleting cells in a column ref a different column Daminc[_23_] Excel Programming 8 December 23rd 05 06:04 PM
Deleting a row by a column value DKY[_17_] Excel Programming 2 October 8th 04 01:33 AM
Deleting a Column Ken[_18_] Excel Programming 1 June 17th 04 10:45 PM
Deleting a row with 0 in column A Steven R. Berke Excel Programming 1 October 2nd 03 01:37 AM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017