Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
In response to my my question with this -what I thought- simple macro I got
two answers, that worked perfectly, but only on one small worksheet. When I tried to apply them on a bigger sheet with 1800 rows (and changinge the macro accordingly) neither would work! (It would also not work for the "original" Range B2:B30, i.e. it did not delete any rows in that range.) The macros we Sub RemoveRow_1() by JE McGimpsey Dim rCell As Range Dim rDelete As Range For Each rCell In Range("B2:B30") (Changed to "B2:B1800") If rCell.Value = 0 Then If rDelete Is Nothing Then Set rDelete = rCell Else Set rDelete = Union(rDelete, rCell) End If End If Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete End Sub and Sub RemoveRow_2() by Vergel Adriano Application.ScreenUpdating = False - added by me: correct? Dim kRow As Long For kRow = 30 To 2 Step -1 (changed to 1800 To 2) With ActiveSheet.Cells(kRow, 2) <- is this for Col. B ? If .Value = 0 Then .EntireRow.Delete End If End With Next kRow End Sub Values were always in Column B, but I guess they could be in any one. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
Oh my God! As I copied the data from the net, I oversaw that the "empty"
cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
test for "", or do you actually mean a space, in which case test for " "
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tatebana" wrote in message ... Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
Hi,
in the line where you check for the value of the cell, test the Text property if it's equal to "0" instead. For example, in the code from JE McGimpsey, instead of this line If rCell.Value = 0 Then try it this way If rCell.Text= "0" Then And in the code from me, you would use If .Text = "0" Then -- Hope that helps. Vergel Adriano "Tatebana" wrote: Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
The problem is: some cells contain " " (1 spaces, others " "(2), others "
"(3), etc. I can get away with it with replacing all spaces and later put them in again. "Bob Phillips" wrote: test for "", or do you actually mean a space, in which case test for " " -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tatebana" wrote in message ... Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
Try testing Len(Trim(value)) for 0.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tatebana" wrote in message ... The problem is: some cells contain " " (1 spaces, others " "(2), others " "(3), etc. I can get away with it with replacing all spaces and later put them in again. "Bob Phillips" wrote: test for "", or do you actually mean a space, in which case test for " " -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tatebana" wrote in message ... Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
No, unfortunately it does not work, probably because some cells contain 1, 2
or three spaces, others contain 0. The row with 0 should also be deleted. One would, I guess, have to test for 0, empty cells ("") and spaces (" ") "Vergel Adriano" wrote: Hi, in the line where you check for the value of the cell, test the Text property if it's equal to "0" instead. For example, in the code from JE McGimpsey, instead of this line If rCell.Value = 0 Then try it this way If rCell.Text= "0" Then And in the code from me, you would use If .Text = "0" Then -- Hope that helps. Vergel Adriano "Tatebana" wrote: Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
Another
If trim(rCell.Value) = "" Then Tatebana wrote: The problem is: some cells contain " " (1 spaces, others " "(2), others " "(3), etc. I can get away with it with replacing all spaces and later put them in again. "Bob Phillips" wrote: test for "", or do you actually mean a space, in which case test for " " -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tatebana" wrote in message ... Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Makro for Deleting Rows
Or...
If trim(rCell.Value) = "" _ rcell.value = 0 Then Tatebana wrote: No, unfortunately it does not work, probably because some cells contain 1, 2 or three spaces, others contain 0. The row with 0 should also be deleted. One would, I guess, have to test for 0, empty cells ("") and spaces (" ") "Vergel Adriano" wrote: Hi, in the line where you check for the value of the cell, test the Text property if it's equal to "0" instead. For example, in the code from JE McGimpsey, instead of this line If rCell.Value = 0 Then try it this way If rCell.Text= "0" Then And in the code from me, you would use If .Text = "0" Then -- Hope that helps. Vergel Adriano "Tatebana" wrote: Oh my God! As I copied the data from the net, I oversaw that the "empty" cells contained blanks...! Shame on me. That, however could lead to the question: Can these blanks be detected? I could replace them first, of course with a simple formula, but that would also eliminate the blanks in text, where they are necessary. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easy Question for deleting rows. | New Users to Excel | |||
this should be easy - filtered rows | Excel Discussion (Misc queries) | |||
combining rows and deleting easy for u experts | Excel Programming | |||
deleting userform or VBA modules by makro | Excel Programming | |||
deleting text is easy... but what about this?!!! | Excel Programming |