Do Until loop vs array

[kualjo]

Usually, when I have something I need done in a column, such as in the following code that checks for blanks and/or invalid entries, I have always done a Do Until loop, where I tell it to run until there is a blank cell in an adjacent column (which has already been checked for blanks). I've seen code written by others that would do the same thing, but as an array. My way works just fine, and I get the results I want, but I'm curious as to whether doing it as an array would be a better approach. Maybe it's just a matter of personal preference, but since I'm self-taught and therefore not so great with VBA, I always wonder how the real pros would do the same thing.


Range("A2").Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If ActiveCell.Value = "" Then
ActiveCell.Value = "[desired text or value]"
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop

What would this code look like if I wrote it to run the column as an array, knowing that some cells could be blank?

[Bob Phillips]

That cannot work, you start at A2 and then try to offset 1 column to the left, there are no columns to the left.

Assuming you mean columns A & B, then I wouldn't use a loop or a array


Dim rng As Range
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("B2").Resize(lastrow - 1).SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then rng.Value = "[desired text or value]"
End With

[snb]

I all depends on your goal
I prefer the use of arrays because you can reduce the amount of interaction with the worksheet and conseuently speeding up your code.

You can use:

sn=sheet1.usedrange.rows(1)
or
sn=sheet1.cells(1).currentregion.rows(1)
or
sn=sheet1.rows(1).specialcells(2)
or
sn=sheet1.cells(1).resize(,sheet1.cells(1,columns.count).end(xltoleft).colu mn)

If you want you change anything in these cells you can change it in the array first.
If ready you can write the array to the resulting range.
Doing so there's no need to use 'application screenupdating =false', or 'application.calculation=xlmanual', etc. because those events will only be triggered once.

[kualjo]

@xld: My mistake on the column. I had some extraneous code that I deleted out, and for whatever unnecessary reason, edited it from 'B1' to 'A1'. :dunno I think I see what your code does, though, and will give it a try.

@snb: As written, my code runs pretty fast, even where I have a large number of rows. Speed isn't really an issue. I'm not sure how or where I would use your suggestions; can you give an example of what it would look like?

Thanks to you both!

[snb]

If you ask for an opinion you get an opinion.
If you ask for a consideration you get a consideration.
If you get a suggestion just try the suggestion in your own situation.

[Jan Karel Pieterse]

What your current code seems to do is find the first row that is empty one column to the left of the selected cell
when doing this with a keyboard you would:
press left arrow key
press control+down arrow
hit down arrow once more
press right arrow key
enter data.

In code this is:

With Range("B2").Offset(0, -1)
If .Offset(1).Value <> "" Then
.End(xlDown).Offset(1, 1).Value = "[Some Value]"
Else
.Offset(0, 1).Value = "[Some Value]"
End If
End With

[Bob Phillips]

I think he is trying to replace all of the blank cells in column B with a value.

[Jan Karel Pieterse]

I stand corrected :-)

[SamT]

I would only use an array if after testing, these took too long. All the work on an array happens in memory, not on the worksheet so it can be noticeably faster with lots of cells to work on. You still need to find the terminator Cell, then set the array to the range as defined by the terminator, loop thru the array and correct any "bad" values, then paste the array back into the Range.

Dim Cel As Range
Set Cel = Range("A2")
Do Until Cel.Offset(0, 1).Value = ""
If Cel.Value = "" Then Cel.Value = "[desired text or value]"
Set Cel = Cel.Offset(1)
Loop

Faster, But not as obvious

LastRow = Range("B2").End(xlDown).Row 'First empty cell in "B" vs xld's lastRow = bottom non-empty cell in "B"

With Columns("A")
For r = 2 To LastRow
If .Cells(r) = "" Then .Cells(r) = "[desired text or value]"
Next r
End With

[Bob Phillips]

Faster, But not as obvious

LastRow = Range("B2").End(xlDown).Row 'First empty cell in "B" vs xld's lastRow = bottom non-empty cell in "B"

With Columns("A")
For r = 2 To LastRow
If .Cells(r) = "" Then .Cells(r) = "[desired text or value]"
Next r
End With

But 10 times slower than mine :)