Re: Enter "Empty" if cell is blank within a variable range

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Norman,

The MsgBox returns a "False". When I look at the result of the Test variable
in the debugger, it shows an empty string: " ".

Dim Test As String
Test = Range("B2").Value

I appreciate your feedback!
ALATL



"Norman Jones" wrote:

Hi ATATL,

My suggeste code will populate all empty cells columns A:B,
up to the last used row in the sheet


What response do you get to:

MsgBox IsEmpty(Range("B2")

?


---
Regards,
Norman

"ALATL" <ALATL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:068896BC-394B-454E-8C97-64516996BFB6@xxxxxxxxxxxxxxxx
Dave,

I have checked the value of the cell with the following.

Dim Test As String
Test = Range("B2").Value

The debugger displays an empty string value.

I have tried to use the Edit > Replace functionality in Excel, but this
does
not work and it says "Cannot find a match".

Also, the total number of rows is variable dependent on the data when the
application is run. It would not be a good things to do a "select all" and
have additional rows accidentally introduced into my data.

I've seen something like this used to look at the last row of data to
begin
looking for a range.


'Set WS = Worksheets("Sheet2")
'BottomRowPos = WS.Range("B65536").End(xlUp).Row

Thanks for your help.


"Dave Peterson" wrote:

If you select the range to fix and
Edit|replace
what: (leave blank)
with: Empty
replace all

does it work?

If it does, then you could just hit Edit|Undo and record a macro when you
do it
manually (one more time).



ALATL wrote:

As simple as this sounds, I can't seem to make this work. I am looking
for
code for the following:

I would like to enter the word "Empty" into a cell if the cell is
blank. The
range can be variable. The only columns that contain an empty cell are
columns B or C which can have any number of rows.

I appreciate any suggestions.

Best.

--

Dave Peterson




.

Relevant Pages

  • Re: Enter "Empty" if cell is blank within a variable range
    ... Dim Test As String ... The debugger displays an empty string value. ... I would like to enter the word "Empty" into a cell if the cell is blank. ...
    (microsoft.public.excel.programming)
  • Re: Removing 0s in cells
    ... A formula cannot empty a cell, it can create an empty string. ... The other consideration here is if the zero was from a formula ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Convertin dbnull to string
    ... I think what you should do is to assign dbnull value to ... >a valid DateTime. ... >> DbNull is a special value and not an empty string. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: empty cells - best way of testing for them
    ... I think it's the safest way to really test if the cell is empty. ... > empty string or it may simply have never been used). ... > null values (for example if that cell has at one point contained data and ...
    (microsoft.public.excel.programming)
  • empty cells - best way of testing for them
    ... I have a simple loop in which I loop through a column of cells.During each ... iteration I wish to test to see if the cell is empty (has no value, ... empty string or it may simply have never been used). ...
    (microsoft.public.excel.programming)