Spy Journal 3.0 Filtering the web for you

I am combining the function of the week with a request I had to write some VBA code to use it. (Note the VBA function is not always the same as the application function).

Here is the question I was asked:

I have two columns - One "AA" and the other "AB"
I want to fill down the column AB based on the results in "AA" but I'm
confused about how to get the individual data from AA2 into AB2 - for
example...
Dim AAcell As Range
Dim ABcell As Range
For Each AAcell In Range("AA:AA")
If IsEmpty(AAcell) Then
    ABcell.Value = ""
Else
    ABcell.Value = Int(AAcell.Value)
End Sub
I'm sure this isnt' right/correct - as it isn't working - LOL - but I'm not
sure how to put it...

Reading this through I understood that the reader wanted to be able to fill a column based on another column. If the first column had a blank cell he wanted that repeated in the new column. All other entries in the new column were to be the INTEGER of the number in the first column. I have provided the code used to make this work at the end of this article. Click Read More to continue.

The INT function simply rounds any number down to the nearest integer. An integer is a whole number (no fractions or decimal places) but can also be positive or negative. The syntax in VBA is exactly the same as in Excel.

The problem could be solved by creating a formula like =IF(AA1="","",INT(AA1)) and copying that from AB1 down to the bottom of the range.

The VBA solution however is very elegant and I tested it on a spreadsheet with a little over 110,000 rows and it ran in less than a second. If you use this in Excel 203, be sure to replace the value for the bottom cell.

Assumptions:

Spreadsheet has non contiguous data in column A (could be any column) that needs to be replicated in column B with the integer of that data. If the cell in column A is empty then it needs to be empty in column B also.

All non empty cells in column A contain values. (Text will break this code but it could be error handled)

Here it is:

Option Explicit
Option Base 1
Dim n As Single, x As Single
Dim newrange(), firstrange()

Sub fillrange()
'this procedure was written by Tim Miller to fill a range based on another range not being empty
'find bottom cell - replace 1048576 with 65535 for excel 2003
    Range("A1048576").End(xlUp).Activate
    firstrange() = Range(ActiveCell, ("A1"))
    n = ActiveCell.Row
   'set the new range array size
    ReDim newrange(n, 1)
    'build new array
    For x = 1 To n
        If Not firstrange(x, 1) = "" Then ' ignore blank cells
            newrange(x, 1) = Int(firstrange(x, 1))
        End If
    Next x
    'paste newrange array in to target range
    Range("B1").Activate
    Range(ActiveCell, ActiveCell.Offset(n - 1, 0)).Select
    Selection = newrange()
End Sub