skipping empty cells that are not empty
 

I am trying to have my macro skip cells that are empty(do not have an I
address in them), however, the empty cells actually have a space or tw
in them so they are not being read as empty...is there any way i ca
check for a cell that does not have data in it...only spaces?

thank

--
quizkiw
-----------------------------------------------------------------------
quizkiwi's Profile: http://www.excelforum.com/member.php...fo&userid=2766
View this thread: http://www.excelforum.com/showthread.php?threadid=47267

skipping empty cells that are not empty
 

for each cell in selection
if len(trim(cell.Value)) = 0 then

else


End if
Next

If the cells actually are blank or contain a space(s)

--
Regards,
Tom Ogilvy

"quizkiwi" wrote in
message ...

I am trying to have my macro skip cells that are empty(do not have an IP
address in them), however, the empty cells actually have a space or two
in them so they are not being read as empty...is there any way i can
check for a cell that does not have data in it...only spaces?

thanks


--
quizkiwi
------------------------------------------------------------------------
quizkiwi's Profile:
http://www.excelforum.com/member.php...o&userid=27668
View this thread: http://www.excelforum.com/showthread...hreadid=472678

skipping empty cells that are not empty
 

At the moment, I do not know the technical commands, but the answer is yes,
you can. Perhaps I will stumble across the appropriate commands as I type
this response. However, ultimately or perhaps the most direct approach, is
to strip the text of the cell of any spaces, and then test if the left
character is a number. Otherwise, to move from left to the end of the length
of the text until a number is found or you have reached the number of
characters in the text.

TRIM([textstring]) will remove all spaces except single space between two
words. So this should also remove spaces before and after the line of text.

if isnumber(value(left(TRIM([textstring]), 1))) = true then
rem the first character of a textstring is actually a number, and
potentially an ip address.
This seemed to work, even with a "blank" cell (Blank as one that contains
only spaces.) From the inside out, it goes like this. Trim removes the
spaces, Left returns the first character of the trimmed text. Value converts
the single character to a number if it is a number, otherwise it leaves it as
text. and isnumber tests to see if the result of value is actually a number
not a string character.


"quizkiwi" wrote:


I am trying to have my macro skip cells that are empty(do not have an IP
address in them), however, the empty cells actually have a space or two
in them so they are not being read as empty...is there any way i can
check for a cell that does not have data in it...only spaces?

thanks


--
quizkiwi
------------------------------------------------------------------------
quizkiwi's Profile: http://www.excelforum.com/member.php...o&userid=27668
View this thread: http://www.excelforum.com/showthread...hreadid=472678

skipping empty cells that are not empty
 

Oops, I basically answered the wrong question. :) However had some of the
same info. :) But alas, not close enough for the requested solution.

"GB" wrote:

At the moment, I do not know the technical commands, but the answer is yes,
you can. Perhaps I will stumble across the appropriate commands as I type
this response. However, ultimately or perhaps the most direct approach, is
to strip the text of the cell of any spaces, and then test if the left
character is a number. Otherwise, to move from left to the end of the length
of the text until a number is found or you have reached the number of
characters in the text.

TRIM([textstring]) will remove all spaces except single space between two
words. So this should also remove spaces before and after the line of text.

if isnumber(value(left(TRIM([textstring]), 1))) = true then
rem the first character of a textstring is actually a number, and
potentially an ip address.
This seemed to work, even with a "blank" cell (Blank as one that contains
only spaces.) From the inside out, it goes like this. Trim removes the
spaces, Left returns the first character of the trimmed text. Value converts
the single character to a number if it is a number, otherwise it leaves it as
text. and isnumber tests to see if the result of value is actually a number
not a string character.


"quizkiwi" wrote:


I am trying to have my macro skip cells that are empty(do not have an IP
address in them), however, the empty cells actually have a space or two
in them so they are not being read as empty...is there any way i can
check for a cell that does not have data in it...only spaces?

thanks


--
quizkiwi
------------------------------------------------------------------------
quizkiwi's Profile: http://www.excelforum.com/member.php...o&userid=27668
View this thread: http://www.excelforum.com/showthread...hreadid=472678