Fill empty cells in a range using the used cell

[bdmsfan]

I have a macro that Color codes all the empty cells in the Range and enters
the legend Missing! to show what attributes need to be entered.

Sub FillEmptyCells()
WCount = Worksheets.Count
For i = 1 To WCount
If Worksheets(WCount - i + 1).Visible Then
Worksheets(WCount - i + 1).Select
RCount = ActiveCell.SpecialCells(xlLastCell).Row
CCount = ActiveCell.SpecialCells(xlLastCell).Column
For j = 1 To RCount
For k = 1 To CCount
If IsEmpty(Worksheets(WCount - i + 1).Cells(j, k)) Then
Worksheets(WCount - i + 1).Cells(j, k) = "Missing!"
Worksheets(WCount - i + 1).Cells(j, k).Interior.ColorIndex = 35
Worksheets(WCount - i + 1).Cells(j, k).Font.Bold = True
End If
Next k
Next j
End If
Next i

End Sub

The idea is that my spreadhsheets will be shrinking over the time because
less number of cells will have missing attributes. The above macro worked
well for a while until my spreadsheets started shrinking and some of the last
rows were all filled with Missing! and color coded, I think it was because
somehow Excel detected them as "active" although they have no values. I read
and article about the "Last Cell" only being reset when you save and the user
who looks at the excel file can't save it (I made that intentionally) so I
guess my problem has to do with that.

On other website I found the sintax to select the Last Used Cell in a row or
column

Sub LastCellInRow()
Range("IV1").End(xlToLeft).Select
End Sub

Sub LastCellInColumn()
Range("A65536").End(xlUp).Select
End Sub

But I don't know how to include this as the range on the previous macro, in
short, I need my range to be from A2 to the last used Column,Row. I hope it
is not to confusing and I appreciate any help.

Thanks
Bruno

 

[Martin Fishlock]

The following uses the usedrange which is from the first cell used to the
last cell used. It may be ok and easier to follow.

Sub FillEmptyCells()
Dim ws As Worksheet
Dim r As Range

For Each ws In ActiveWorkbook.Worksheets

If ws.Visible Then
If ws.UsedRange.Address <> "$A$1" Then
For Each r In ws.UsedRange
If IsEmpty(r) Then
r.Value = "Missing!"
r.Interior.ColorIndex = 35
r.Font.Bold = True
End If
Next r
End If
End If
Next ws
Set r = Nothing
Set ws = Nothing
End Sub