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

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.

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

Hi Alatl,

Try somethimg like:

'================
Public Sub Tester()
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
On Error Resume Next
Set Rng = Intersect(.UsedRange, .Columns("A:B")). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With

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

---
Regards,
Norman


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

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

Norman,

This part of the code never executes and the cell does not get populated
with the empty value.

Rng.Value = sStr

Best,
ALATL

"Norman Jones" wrote:

Hi Alatl,

Try somethimg like:

'================
Public Sub Tester()
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
On Error Resume Next
Set Rng = Intersect(.UsedRange, .Columns("A:B")). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With

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

---
Regards,
Norman


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

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

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

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

If that statement isn't executed, then you don't have empty cells in that range.

Maybe you converted formulas that evaluated to "" to values?

Or maybe you used the spacebar to make the cell look empty?

ALATL wrote:

Norman,

This part of the code never executes and the cell does not get populated
with the empty value.

Rng.Value = sStr

Best,
ALATL

"Norman Jones" wrote:

Hi Alatl,

Try somethimg like:

'================
Public Sub Tester()
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
On Error Resume Next
Set Rng = Intersect(.UsedRange, .Columns("A:B")). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With

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

---
Regards,
Norman


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

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

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

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

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

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

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

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

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

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

Norman,

This code handled my issue. Thanks for your input.

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

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

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

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

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

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

Dave,

Yes, I did. But the data contained two white spaces. Since I am not
responsible for the data source, I needed code that would trim any number of
white spaces on the cell. That's why I used the trim function.

Best,
ALATL

"Dave Peterson" wrote:

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