Update Excel Field

[Adams]

I have a spreadsheet that I would like to place a value of XX into the fields that are null. The update would take place in A Column.
Example
If cells in column A are null then insert XX

How do I do about this in VB. The macro would have to know the last row.

Thank you.

 

[SkipVought]

Hi,

How is the last row defined?

with activesheet.usedrange
  r1 = .row
  r2 = r1 + .rows.count - 1
end with
for r = r1 to r2
  with cells(r, "A")
    if .value = "" then
       .value = "XX"
    end if
  end with
next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

No cell in excel may be null.
Take a look at the SpecialCells(xlCellTypeBlanks) method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Adams,

PH is correct; cells cannot be null.

If you want to replace blanks within the used range in column A, with "XX" then this will do it ..

[blue]ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlanks).Formula = "XX"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.