Assigning ranges with empty cells

Assigning ranges with empty cells

• fel lobo

  Joe_D wrote:

  I am assigning ranges with empty cells to array variables. However, when I reassign the variables with empty values back to a range, the empty cells now contain 0 (zero). I really want the cell to still be empty instead of containing a 0. How can I fix this

  It would be easier to suggest a solution if you provided more information: how the array variables are declared, what the range looks like, what code is used to get the data into the array, to process the array, and to put the data back into the sheet.

  - Jon
  -------
  Jon Peltier, Microsoft Excel MVP
  Tutorials and Custom Solutions
  http://PeltierTech.com
  _______

  
  
  

• Darren Grove

  On checking further, you are right.  It turns out that I was multiplying the blanks by a number, which I guess forces the value to be a zero instead of a blank.

  Thanks.

  
  

• xRuntime

  Both your lines of code work (the second line keeps the cell blank and not 0 so may you are doing something during your manipulation of the array).

  Post the manipulation code and maybe can help

  ChasAA

  
  

• Dave Patricio

  Not quite.

  I am actually assigning a range of cells to an array variable.  I manipulate the array variable, but there may still be some blank values in the array variable.  When I reassign the array variable with the blank values to another range, the blank values are written as zeroes, not blanks.  See below

  Dim varArray As Variant

  varArray = Range(Cells(1,1),Cells(1,20)).Value     >> some of the cells are blank

  manipulate varArray values that are not blank  >>  the blank values of the array variable have not been changed and are still supposed to be blank

  Range(Cells(2,1),Cells(2,20)).Value = varArray      >> the blank values of array are now written as zeroes in the cells in row 2, but I want them to be blank.

   

  
  

• nature0276

  The following will work:

  Define Range1 to be a selection of cells (Insert-Name- etc)

  Define Range2 to be the same number of cells (Insert-Name-etc)

  Put some values in Range1 eg

  1

  2

  "blank"

  3

  4

  5

  6

  Then run the macros

  Is this what you were after

  ChasAA

  Sub assignToVar()
  Dim varArray(10)
  Dim counter As Integer
  Range("Range1").Select
  counter = 1
  For Each c In Selection
  varArray(counter) = c.Value
  counter = counter + 1
  Next
  Call assignToCell(varArray)
  End Sub

  Sub assignToCell(varArray)
  Dim counter As Integer
  Range("Range2").Select
  counter = 1
  For Each c In Selection
  c.Value = varArray(counter)
  counter = counter + 1
  Next
  End Sub
  

  [Code Ends]

  
  
• Assigning ranges with empty cells