0

What I am doing is very simple - selecting a union of columns which contain numbers stored as text and converting them. Every time this runs, all even union numbered columns of data are getting cleared.

Union(Columns(19), Columns(22), Columns(25), Columns(28), Columns(31), Columns(34), Columns(37), Columns(40), Columns(43), Columns(46)).Select
With Selection
.Value = .Value
End With

I've looked though my entire code multiple times are cant figure why this is behaving so weird. any help is greatly appreciated.

Community
  • 1
  • 1
marv
  • 962
  • 3
  • 9
  • 17

2 Answers2

1

The Value property of a discontiguous range only returns the first area of that range. When you then try and assign that value (array, in this case) back to a discontiguous range, you get strange results. For this particular case, every second column will get the value of the first cell in the first area.

You should loop through the areas in your range.

For each rArea in Selection.Areas
   rarea.value2 = rarea.value2
Next rarea
Rory
  • 32,730
  • 5
  • 32
  • 35
0

Try to avoid using Select, and fully qualify your ranges. This makes things easier to diagnose and more robust...

Dim myRange As Range
With ThisWorkbook.Sheets("Sheet1")
    Set myRange = Union(.Columns(19), .Columns(22), .Columns(25)) ' etc.
End With

Now if you're trying to convert text to numbers, you might be better off using the NumberFormat property as discussed here: What are .NumberFormat Options In Excel VBA?

Looping through range areas and number-formatting:

Dim area As Range
For Each area In myRange.Areas
    area.NumberFormat = 0 ' for numbers, could still use area.Value = area.Value
Next area
Wolfie
  • 27,562
  • 7
  • 28
  • 55