I'm surprised a simple Edit|Replace didn't work.

Did you try that?

ALATL wrote:

Norman,

This code addressed my issue. I wanted to share it with the grpup.

Best,

Sub Empty_()

Dim LastRow As Long

Sheets("Sheet2").Select
LastRow = ActiveSheet.Range("A65536").End(xlUp).Row

'Process Column B
Range("B2").Select

Do
If Trim(ActiveCell.Text) = "" Then
ActiveCell.Value = "Empty"
End If

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Row = LastRow

'Process Column C
Range("C2").Select

Do
If Trim(ActiveCell.Text) = "" Then
ActiveCell.Value = "Empty"
End If

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Row = LastRow

End Sub

"Norman Jones" wrote:

Hi Alatl,

Try the following version:


'================
Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Const sStr As String = "Empty"

Set WB = Workbooks("MyBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

With SH
Set Rng = Intersect(.UsedRange, .Columns("A:B"))
With Rng
.Replace Space(1), vbNullString
.Replace Chr(160), vbNullString
On Error Resume Next
Set Rng = .SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With
End With

If Not Rng Is Nothing Then
Rng.Value = sStr
End If
End Sub
'<<================


---
Regards,
Norman



"ALATL" wrote in message
...
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" wrote in message
...
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








--

Dave Peterson