Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
while deleting rows it finds an error - error trapping
Okay, my code seems to be working but it stops after deleting the first two
rows. It must find an error then return to 0 but what is the error? The only thing I see is there are two cells together in column M that are blank. There are a whold bunch more. HOw do I trap for the error so this script will finish. thanks, sorry for the multiple posts. -----------code------ Sub DeleteRowsBlankMColumns() With ActiveSheet On Error Resume Next .Columns("M").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete On Error GoTo 0 End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
while deleting rows it finds an error - error trapping
From your previous post give this a try. It is probalby not an error but
rather blank space characters or the blanks are the result of a formula... How about this. It should delete all of the blank whether they are the result of a formula or if the cell contains blank spaces... It is similar to what you first posted... Sub DeleteBlanks() Dim rngToSearch As Range Dim rng As Range Dim rngToDelete As Range With ActiveSheet On Error Resume Next Columns("M").SpecialCells(xlCellTypeBlanks).Entire Row.Delete On Error GoTo 0 Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) For Each rng In rngToSearch If Trim(rng.Value) = "" Then If rngToDelete Is Nothing Then Set rngToDelete = rng Else Set rngToDelete = Union(rng, rngToDelete) End If Next rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete End With End Sub -- HTH... Jim Thomlinson "Janis" wrote: Okay, my code seems to be working but it stops after deleting the first two rows. It must find an error then return to 0 but what is the error? The only thing I see is there are two cells together in column M that are blank. There are a whold bunch more. HOw do I trap for the error so this script will finish. thanks, sorry for the multiple posts. -----------code------ Sub DeleteRowsBlankMColumns() With ActiveSheet On Error Resume Next .Columns("M").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete On Error GoTo 0 End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
while deleting rows it finds an error - error trapping
That is a very nice script. I am sure there is dirty data in it. It comes
from a SQL server or is downloaded as a report. This company uses excel for everything instead of databases. some files they copy several times. tnx, "Jim Thomlinson" wrote: From your previous post give this a try. It is probalby not an error but rather blank space characters or the blanks are the result of a formula... How about this. It should delete all of the blank whether they are the result of a formula or if the cell contains blank spaces... It is similar to what you first posted... Sub DeleteBlanks() Dim rngToSearch As Range Dim rng As Range Dim rngToDelete As Range With ActiveSheet On Error Resume Next Columns("M").SpecialCells(xlCellTypeBlanks).Entire Row.Delete On Error GoTo 0 Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp)) For Each rng In rngToSearch If Trim(rng.Value) = "" Then If rngToDelete Is Nothing Then Set rngToDelete = rng Else Set rngToDelete = Union(rng, rngToDelete) End If Next rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete End With End Sub -- HTH... Jim Thomlinson "Janis" wrote: Okay, my code seems to be working but it stops after deleting the first two rows. It must find an error then return to 0 but what is the error? The only thing I see is there are two cells together in column M that are blank. There are a whold bunch more. HOw do I trap for the error so this script will finish. thanks, sorry for the multiple posts. -----------code------ Sub DeleteRowsBlankMColumns() With ActiveSheet On Error Resume Next .Columns("M").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete On Error GoTo 0 End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting a range of rows based on a variable; syntax error | Excel Discussion (Misc queries) | |||
runtime 1004 error on deleting rows with blank cells | Excel Programming | |||
Error when deleting rows | Excel Programming | |||
Code deleting all rows in error | Excel Programming | |||
Deleting rows based upon Error conditions | Excel Programming |