Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with null values in "M" column
I'm trying to delete the rows where the cell "M" has a null or blank value.
I borrowed this script and it compiles. The question I have is what is the test if range to delete Is NOthing then.... mean. Does that stand for null?? Also what is the union of the rng to delete? Do I just do if IsNull(rng) set rng to delete = rng I realize they are trying to be careful but I don't quite understand what is going on. thanks ---------------code--------------------------- Sub deleteRows() Dim Rng As Range Dim rngToSearch As Range Dim rngToDelete As Range With ActiveSheet Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) End With For Each Rng In rngToSearch If IsNull(Rng) Then If rngToDelete Is Nothing Then Set rngToDelete = Rng Else Set rngToDelete = Union(rngToDelete, Rng) End If End If Next Rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with null values in "M" column
"Janis" wrote in message ... I'm trying to delete the rows where the cell "M" has a null or blank value. I borrowed this script and it compiles. The question I have is what is the test if range to delete Is NOthing then.... mean. Does that stand for null?? No, it is checkong to see whether any items to delete have already been identified, so as to determine whether to prime the ranbge, or add to it (Union) Also what is the union of the rng to delete? Do I just do if IsNull(rng) set rng to delete = rng See above. The code doesn't work for me. this does Dim rngToSearch As Range Dim rngToDelete As Range Dim rng As Range With ActiveSheet Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) End With For Each rng In rngToSearch If IsEmpty(rng) Then If rngToDelete Is Nothing Then Set rngToDelete = rng Else Set rngToDelete = Union(rngToDelete, rng) End If End If Next rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete But you can do it simpler Dim rngToDelete As Range With ActiveSheet On Error Resume Next Set rngToDelete = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)).SpecialCells(xlCellTypeBlanks) On Error GoTo 0 End With If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with null values in "M" column
janis,
the code you have is a complicated way of doing what you want but basically it searches your rand for Null (empty) cells and buils then into a new range called rngtodelete At the end it deletes the lot in one hit Much simpler Sub deleteit() Set myRange = Range("M1", Range("M65536").End(xlUp)) myRange.SpecialCells(xlCellTypeBlanks).EntireRow.D elete End Sub Mike "Janis" wrote: I'm trying to delete the rows where the cell "M" has a null or blank value. I borrowed this script and it compiles. The question I have is what is the test if range to delete Is NOthing then.... mean. Does that stand for null?? Also what is the union of the rng to delete? Do I just do if IsNull(rng) set rng to delete = rng I realize they are trying to be careful but I don't quite understand what is going on. thanks ---------------code--------------------------- Sub deleteRows() Dim Rng As Range Dim rngToSearch As Range Dim rngToDelete As Range With ActiveSheet Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) End With For Each Rng In rngToSearch If IsNull(Rng) Then If rngToDelete Is Nothing Then Set rngToDelete = Rng Else Set rngToDelete = Union(rngToDelete, Rng) End If End If Next Rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with null values in "M" column
The first time a row-to-be-deleted is encountered, nothing has yet been
assigned to the variable rngToDelete, thus is is equal to "nothing" and it is safe to assign the current address to the variable. When subsequent rows-to-be-deleted are encountered, you want to aggregate the new row(s) with the previously identified row(s), thus the purpose of the UNION() clause is to assign the new row(s) without losing what was already assigned to the variable. "Janis" wrote: I'm trying to delete the rows where the cell "M" has a null or blank value. I borrowed this script and it compiles. The question I have is what is the test if range to delete Is NOthing then.... mean. Does that stand for null?? Also what is the union of the rng to delete? Do I just do if IsNull(rng) set rng to delete = rng I realize they are trying to be careful but I don't quite understand what is going on. thanks ---------------code--------------------------- Sub deleteRows() Dim Rng As Range Dim rngToSearch As Range Dim rngToDelete As Range With ActiveSheet Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) End With For Each Rng In rngToSearch If IsNull(Rng) Then If rngToDelete Is Nothing Then Set rngToDelete = Rng Else Set rngToDelete = Union(rngToDelete, Rng) End If End If Next Rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with null values in "M" column
By the way, I tried this one and it only deleted the first 3 columns with
blank cells in M column and then stopped. "Bob Phillips" wrote: "Janis" wrote in message ... I'm trying to delete the rows where the cell "M" has a null or blank value. I borrowed this script and it compiles. The question I have is what is the test if range to delete Is NOthing then.... mean. Does that stand for null?? No, it is checkong to see whether any items to delete have already been identified, so as to determine whether to prime the ranbge, or add to it (Union) Also what is the union of the rng to delete? Do I just do if IsNull(rng) set rng to delete = rng See above. The code doesn't work for me. this does Dim rngToSearch As Range Dim rngToDelete As Range Dim rng As Range With ActiveSheet Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) End With For Each rng In rngToSearch If IsEmpty(rng) Then If rngToDelete Is Nothing Then Set rngToDelete = rng Else Set rngToDelete = Union(rngToDelete, rng) End If End If Next rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete But you can do it simpler Dim rngToDelete As Range With ActiveSheet On Error Resume Next Set rngToDelete = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)).SpecialCells(xlCellTypeBlanks) On Error GoTo 0 End With If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with null values in "M" column
But only if it has been declared!
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duke Carey" wrote in message ... The first time a row-to-be-deleted is encountered, nothing has yet been assigned to the variable rngToDelete, thus is is equal to "nothing" and it is safe to assign the current address to the variable. When subsequent rows-to-be-deleted are encountered, you want to aggregate the new row(s) with the previously identified row(s), thus the purpose of the UNION() clause is to assign the new row(s) without losing what was already assigned to the variable. "Janis" wrote: I'm trying to delete the rows where the cell "M" has a null or blank value. I borrowed this script and it compiles. The question I have is what is the test if range to delete Is NOthing then.... mean. Does that stand for null?? Also what is the union of the rng to delete? Do I just do if IsNull(rng) set rng to delete = rng I realize they are trying to be careful but I don't quite understand what is going on. thanks ---------------code--------------------------- Sub deleteRows() Dim Rng As Range Dim rngToSearch As Range Dim rngToDelete As Range With ActiveSheet Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) End With For Each Rng In rngToSearch If IsNull(Rng) Then If rngToDelete Is Nothing Then Set rngToDelete = Rng Else Set rngToDelete = Union(rngToDelete, Rng) End If End If Next Rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I don't want to graph "null" values? | Charts and Charting in Excel | |||
OTHER options for chart not plotting null ("") or zero values | Charts and Charting in Excel | |||
Delete rows containing "ISA" in column A only, starting from row 6 | Excel Programming | |||
cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) | |||
"IF"- "THEN" type Formula based on Null value | Excel Worksheet Functions |