Evaluating Range for NULL or "" and replacing with .Clear

[VenturSum]

Programmers,

My imported dataset has null values.

Currently I loop through the columns and rows; and use this is code:

IF .CELL(lRow, lCol).Value = "" THEN .CELL(lRow,lCol).Clear

This method takes a long time.

Could the .Range &/or Evalute method be used?

Something like:
With Range("A1", "Z"&lRowLast)
.CLEAR IF ISNULL
End With

Any thoughts?

Respectfully,

John In Annapolis.

 

[Fluff]

If they are nullstrings try

With ActiveSheet.UsedRange
   .Value = .Value
End With

If not select a cell that you want to clear & run this

MsgBox Asc(ActiveCell)

What does the message box say?

 

[shg]

If you don't have your heart set on VBA,

Select the column(s) of interest

Do Ctrl+F (Find), leave Find what blank, press Find All

Do Ctrl+A to select all those cells, the either press Delete to clear contents, or Clear > Clear All to clear everything.

 

[VenturSum]

Team.

I must use VBA.

Is there a way to use With .Range() or Set Range
to set the cells to .Clear where .Value = "" ?

This method would be much faster than looping thru the columns and rows.

In XML, .Clear removes the cell reference completely,
<v><v>not just emptying the value attribute.


Respectfully,

John in Annapolis, MD</v></v>

 

[Rick Rothstein]

Team.

I must use VBA.

Is there a way to use With .Range() or Set Range
to set the cells to .Clear where .Value = "" ?

This method would be much faster than looping thru the columns and rows.

Did you see what Fluff posted in Message #2 ... simply change the object of the With statement to your needed range.

 

[shg]

Did you see what Fluff posted in Message #2

I don't think that replaces null strings with empty cells.

 

[Rick Rothstein]

I don't think that replaces null strings with empty cells.

If by null string you mean "", then I am pretty sure .Value = .Value does convert that to an empty cell..

 

[shg]

I retract that, you and Fluff are correct.

So ashamed ...

 

[VenturSum]

Team.

I am AMAZED that .Value = .Value clears out the null values.
Even in the XML code, I did not see references to the cells.

This was a great piece of code and really sped up the process.

Thank you,

John, In Annapolis, MD

 

[Fluff]

Glad we could help & thanks for the feedback