Geek Answers Handbook

Keep null strings at assignment. Value with VBA

(edit: 2 more solution restrictions added)

I have a table (listobject wise) in which I need to duplicate certain rows. I use SourceListRow.Range.Value2 = DestListRow.Range.Value2 for efficiency reasons (copying entire ranges at a time). All formula columns after copied cell ranges are automatically expanded in new rows and act on the copied data. I am using Excel 2010 here on Windows.

However, although I have already used this type of code for aeons, I just stumbled upon a weirdness when using Range.Value / Range.Value2: if you assigned it an empty string, the final cell value will not be an empty string, but it will be empty. That is: the data is not copied reliably, and the copy may differ from the source, especially if sequential formulas use ISBLANK, etc. On her. Therefore, the same formula will have different results when working with a copy and a source.

See test code below. Open a new, empty Excel workbook, go to VBA, add a new module and add the following code:

Sub Test()
  ActiveSheet.Range("a1").Formula = "="""""
  ActiveSheet.Range("b1").Formula = "=isblank(a1)"
  ActiveSheet.Range("c1").Value2 = TypeName(ActiveSheet.Range("a1").Value2)

  ActiveSheet.Range("a2").Value2 = ActiveSheet.Range("a1").Value2
  ActiveSheet.Range("b2").Formula = "=isblank(a2)"
  ActiveSheet.Range("c2").Value2 = TypeName(ActiveSheet.Range("a2").Value2)

  ActiveSheet.Range("a3").Value2 = ""
  ActiveSheet.Range("b3").Formula = "=isblank(a3)"
  ActiveSheet.Range("c3").Value2 = TypeName(ActiveSheet.Range("a3").Value2)

  ActiveSheet.Range("a4").Formula = ActiveSheet.Range("a1").Formula
  ActiveSheet.Range("b4").Formula = "=isblank(a4)"
  ActiveSheet.Range("c4").Value2 = TypeName(ActiveSheet.Range("a4").Value2)

  Call ActiveSheet.Range("a1").Copy
  Call ActiveSheet.Range("a5").PasteSpecial(xlPasteValues)
  ActiveSheet.Range("b5").Formula = "=isblank(a5)"
  ActiveSheet.Range("c5").Value2 = TypeName(ActiveSheet.Range("a5").Value2)
End Sub

Then run it and look at the sheet;

  • B1 tells FALSE (as it should - the cell is not empty), and C1 says "String" (the cell value is really an empty string);
  • B2 TRUE, A1 as-is; C2 "Empty", "String", ;
  • , B3 TRUE, ; C3 "Empty", ;
  • Range.Formula OtherRange.Formula ( String B4), , !
  • , GUI A5, , , Copy/Paste-As-Val is-a-empty-string...

?

  • Range.Copy/Range.PasteSpecial(xlPasteValues) , ;
  • .Formula , ;
  • , , , value = "", , ...
  • , (a.k.a. ListObject); , , , kludge.
  • Range.Find Range.Replace , Find/Replace.

!

+4
1

SpecialCells (xlCellTypeBlank) AutoFilter -. ? , -, .Formula = "=""""" ?

, . "ChangeMe"; "ChangeMe" "=" "" ", Range.Replace.

Sub Test2()

ActiveSheet.Range("A2:A6").SpecialCells(xlCellTypeBlanks).Interior.Color = 255 'Just to prove that xlCellTypeBlanks only selects actual blanks

ActiveSheet.Range("A1:A6").AutoFilter Field:=1, Criteria1:="=" 'Shows both actual blanks and ZLS/null strings
ActiveSheet.Range("A1:A6").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True 'Be able to use SpecialCells(xlCellTypeVisible) to reference all ZLS
ActiveSheet.Range("A2:A6").SpecialCells(xlCellTypeVisible).Value2 = "ChangeMe"
'Now unfilter and unhide your data, copy using .Value2, then .Replace "ChangeMe" with "="""""

End Sub

, , , - , .

: . .

, , .AutoFilter , . ,.AutoFilter .

- :

Sub CopyAndClearFakeBlanks()

Dim WSSource As Worksheet
Dim WSDest As Worksheet
Dim LRow As Long
Dim LPasteRow As Long

Set WSSource = Sheets("Source Data")
Set WSDest = Sheets("Paste Here")

LRow = WSSource.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row 'Note that this will ignore blanks, so you may want to use a different method for finding your last row. Depends on how your users would need the data if there are trailing blanks.

On Error Resume Next
LPasteRow = 2 'Always need at least one row before the data for filtering properly; you can delete after if necessary
LPasteRow = WSDest.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row + 1
WSDest.AutoFilterMode = False
On Error GoTo 0 'ofc use proper error handling in your actual code

WSDest.Range("A" & LPasteRow & ":A" & LPasteRow + LRow - 1).Value2 = WSSource.Range("A1:A" & LRow).Value2
WSDest.Range("A" & LPasteRow - 1 & ":A" & LPasteRow + LRow).AutoFilter field:=1, Criteria1:="=" 'Show blank or ZLS only

On Error Resume Next
WSDest.Range("A" & LPasteRow & ":A" & LPasteRow + LRow).SpecialCells(xlCellTypeVisible).Clear 'Turn them into true blanks
WSDest.ShowAllData
WSDest.AutoFilterMode = False
On Error GoTo 0

Set WSDest = Nothing
Set WSSource = Nothing

End Sub
0

More articles:


All Articles