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.
"Greg Snidow" wrote:
> 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?
>
> "Thomas [PBD]" wrote:
>
> > 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.
> >
> >
> > "Greg Snidow" wrote:
> >
> > > 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
> > >
|
Similar Threads
