Delete rows with macro not working

[Greg Snidow]

Greetings all. I have a macro that, among other things, deletes all rows
from a sheet where ever a cell in one column is blank. The part of the macro
in question is below.

' Delete rows with no job number
Dim rng As Range
On Error Resume Next
Set rng = Columns(4).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

The problem is that recently it does not work for some rows on a report from
a particular user. The cells in column 4 appear to be blank, but when I run
the macro, they are not deleted. If I first highlight all the cells that
appear to be blank, and hit delete, then run the macro, it works fine, so
there seems to be some zero length strings, or just blank spaces in some of
the cells. My question is, how can I include these cells in the set to be
deleted? Thank you.

Greg Snidow

 

[Thomas [PBD]]

Greg,

Have you used the =ISBLANK(<cell>) function to determine if the cells are in
fact empty? If so, and it returns "FALSE" then you have some underlying code.

You would have to figure out what exactly are in the cells, are they spaces,
are they Hardcode returns (which appear as null spots but are infact coding
into the cells), or a special character of some type. There are a lot of
errors sometimes when users attempt to import excel information from the
Internet primarily the Hardcode returns.
I believe without the actual items in the cells, you might have a hard time
accomplishing this task. Maybe you can watch the user enter the information
one day and figure out where he is going astray.

 

[Greg Snidow]

Thomas,
Thanks for the quick reply. Indeed, the ISBLANK function returns FALSE for
the rows in question. The report is all manual entry, no web coding
anywhere. The users simply copy yesterdays sheet into a new sheet, delete
all the data, then type in new data for today. The user of the one that is
broken is in another state, so I can not really see what she is doing. The
cell in question contains a job number that is *always* between 7 and 11
characters long. Could I somehow alter the macro to delete all rows where
the LEN of that cell < 7? The LEN() function returns a value of 1 for all the
seemingly blank cells, so they appear to be just spaces. Is there a way to
determine what they are?

Greg,

Have you used the =ISBLANK(<cell>) function to determine if the cells are in
fact empty? If so, and it returns "FALSE" then you have some underlying code.

You would have to figure out what exactly are in the cells, are they spaces,
are they Hardcode returns (which appear as null spots but are infact coding
into the cells), or a special character of some type. There are a lot of
errors sometimes when users attempt to import excel information from the
Internet primarily the Hardcode returns.
I believe without the actual items in the cells, you might have a hard time
accomplishing this task. Maybe you can watch the user enter the information
one day and figure out where he is going astray.

--
--Thomas [PBD]
Working hard to make working easy.

Greetings all. I have a macro that, among other things, deletes all rows
from a sheet where ever a cell in one column is blank. The part of the macro
in question is below.

' Delete rows with no job number
Dim rng As Range
On Error Resume Next
Set rng = Columns(4).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

The problem is that recently it does not work for some rows on a report from
a particular user. The cells in column 4 appear to be blank, but when I run
the macro, they are not deleted. If I first highlight all the cells that
appear to be blank, and hit delete, then run the macro, it works fine, so
there seems to be some zero length strings, or just blank spaces in some of
the cells. My question is, how can I include these cells in the set to be
deleted? Thank you.

Greg Snidow

 

[Thomas [PBD]]

Greg,

You could do a either of those. You can amend your macro to exclude
anything either greater than 11 and/or less than 7, considering that you
trust the data. Secondly, you could use =CODE(<cell>) function to determine
the entry in the cell. 32 is a space, 13 is a carriage return, and 10 is a
hardcode return (ALT+Enter in the cell). In the VBA, the Asc() function will
determine the number. You could Dim the string of the cell in question and
use the Asc(<rng>) to exclude it with an If statement.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

Thomas,
Thanks for the quick reply. Indeed, the ISBLANK function returns FALSE for
the rows in question. The report is all manual entry, no web coding
anywhere. The users simply copy yesterdays sheet into a new sheet, delete
all the data, then type in new data for today. The user of the one that is
broken is in another state, so I can not really see what she is doing. The
cell in question contains a job number that is *always* between 7 and 11
characters long. Could I somehow alter the macro to delete all rows where
the LEN of that cell < 7? The LEN() function returns a value of 1 for all the
seemingly blank cells, so they appear to be just spaces. Is there a way to
determine what they are?

Greg,

Have you used the =ISBLANK(<cell>) function to determine if the cells are in
fact empty? If so, and it returns "FALSE" then you have some underlying code.

You would have to figure out what exactly are in the cells, are they spaces,
are they Hardcode returns (which appear as null spots but are infact coding
into the cells), or a special character of some type. There are a lot of
errors sometimes when users attempt to import excel information from the
Internet primarily the Hardcode returns.
I believe without the actual items in the cells, you might have a hard time
accomplishing this task. Maybe you can watch the user enter the information
one day and figure out where he is going astray.

--
--Thomas [PBD]
Working hard to make working easy.

Greetings all. I have a macro that, among other things, deletes all rows
from a sheet where ever a cell in one column is blank. The part of the macro
in question is below.

' Delete rows with no job number
Dim rng As Range
On Error Resume Next
Set rng = Columns(4).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

The problem is that recently it does not work for some rows on a report from
a particular user. The cells in column 4 appear to be blank, but when I run
the macro, they are not deleted. If I first highlight all the cells that
appear to be blank, and hit delete, then run the macro, it works fine, so
there seems to be some zero length strings, or just blank spaces in some of
the cells. My question is, how can I include these cells in the set to be
deleted? Thank you.

Greg Snidow