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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
'<<================
 
Norman,

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

Rng.Value = sStr

Best,
ALATL
 
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).
 
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?
 
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.
 
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")

?
 
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
 
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 said:
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
Click to expand...
 
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,

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
 
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
 
Back
Top