VBA shared file macro execution question

[Pinaceous]

Hi All,

I've created a shared macro enabled document.

When I try to execute a removing blanks code:


Provided as reference:

Sub Macro19()
'
' Macro19 Macro
' Removing Blanks
'

Sheets("Sheet4").Select
    Dim r As Range, rw As Range, Rws As Long
Set r = Range("B10:Q179")  'Change range to suit
Application.ScreenUpdating = False
For Each rw In r.Rows
    If Application.CountA(rw) = 0 Then
        rw.Value = "#N/A"
        Rws = Rws + 1
    End If
Next rw
With r
       On Error Resume Next
       .SpecialCells(xlCellTypeConstants, xlErrors).Delete Shift:=xlUp
       .Cells(.Rows.Count + 1, 1).Resize(Rws, r.Columns.Count).Insert Shift:=xlDown
       On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

It produces

#N/A

in the inserted row that I'd expect it to be blank!?

For example, When I test out this code prior to creating the

shared macro enabled document

it works like a charm without a problem.

Anyone, know how I can get around this without making the document un-shared??


Thank you!
Pinaceous