Broken VBA Loop

Question

I'm sure this is simple I just can't find anything on the Web.

I'm writing a Macro to format XL spreadsheets that i download from a 3rd party application. They come formatted all wacky so i'm trying to make it easier to get the data we need from them.

This is a simple VBA Do Loop that causes the cells in Column BL to update. The data in these cells contain line breaks which don't show up until you double click in the cell. The VBA below causes an update to the cells which achieves the same effect, just with less work. However it is currently crashing excel and I can't figure out why. It works in a single instance, but when I loop -- BOOM!!! -- frozen. Any help would be gently appreciated.

Sub updateCell()

    Dim currentValue As String
    ActiveSheet.Range("BL1").Select

    Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
        currentValue = ActiveCell().Value
        ActiveCell().Value = currentValue & ""
    Loop

End Sub

It seems likely that what you think are empty cells, are not in fact empty, and the symptom you describe is simply what happens when Excel tries to iterate each of 1.048,576 cells in the column. Try `Do Until Trim(ActiveCell.Value) = ""` maybe? — David Zemens, Oct 14 '14 at 03:23
You may also want to read [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) about how (and why) to avoid relying on `Activate` and `Select` methods. — David Zemens, Oct 14 '14 at 03:24

L42 · Accepted Answer · 2014-10-14T06:54:05.007

2

Try something a bit more direct:

With ActiveSheet 
    lrow = .Range("BL" & .Rows.Count).End(xlUp).Row '~~> find last row on BL
    With .Range("BL1:BL" & lrow) '~~> work on the target range
        .Value = .Value '~~> assign its current value to it
    End With
End With

Above code is like manually pressing F2 then pressing Enter.

Edit1: Explanation on getting the last row

ActiveSheet.Rows.Count '~~> Returns the number of rows in a sheet which is 1048576
MsgBox ActiveSheet.Rows.Count '~~> run this to confirm

So this line actually concatenates BL to 1048576.

.Range("BL" & .Rows.Count) '~~> Count is a property of the Rows Collection

Same as:

.Range("BL" & 1048576)

And same as:

.Range("BL1048576")

Then to get to the last row, we use Range Object End Method.

.Range("BL" & .Rows.Count).End(xlUp)

So basically, above code go to Cell BL1048576 then like manually pressing Ctrl+Arrow Up.
To return the actual row number of the range, we use the Range Object Row property.

lrow = .Range("BL" & .Rows.Count).End(xlUp).Row

See here more about With Statement.
It has the same effect (with your code) without the loop. HTH

But if what you want is to remove Line Breaks produced by Alt+Enter on a cell, try below:

Dim lrow As Long, c As Range
With ActiveSheet
    lrow = .Range("BL" & .Rows.Count).End(xlUp).Row
    For Each c In .Range("BL1:BL" & lrow)
        c.Value = Replace(c.Value, Chr(10), "")
    Next
End With

Where Chr(10) is the equivalent of Line Break replaced with "" using Replace Function.

edited Oct 14 '14 at 06:54

answered Oct 14 '14 at 03:22

L42

19,427
11
44
68

1

You'll get a type mismatch error on this. Why not simply `.Value` = `.Value`? – David Zemens Oct 14 '14 at 03:25
@DavidZemens Ah I posted it without testing. I just copied what OP posted, hold on a sec I'll change it. But I'm not sure if it will get the result the OP wanted. – L42 Oct 14 '14 at 03:26
I think it will. Not quite sure why OP is deliberately appending a null string to each cell's `.Value`, seems like pointless operation. – David Zemens Oct 14 '14 at 03:27
1

@DavidZemens Yeah, regardless I provided another solution that hopefully will get what OP wants. – L42 Oct 14 '14 at 03:41
This should work. I need the line breaks, they just don't appear in the cell by default. I have to cause an update to the cell to make the line breaks visable. Many thanks to all the posters. – Jamie Marshall Oct 14 '14 at 06:26
@JamieMarshall oh So the first code is what you need after all. :) Glad it help. [See accepting answers](http://stackoverflow.com/help/someone-answers) as one way of thanking in SO. You can also answer other peoples questions. – L42 Oct 14 '14 at 06:28
@JamieMarshall See my edit. As for the *With Statement*, you can browse the link. Basically it is way of assessing multiple property of an object. – L42 Oct 14 '14 at 06:38
Thanks, I understand with statements just fine, it's how you found the lrow that confused me. I have it now though. A truly elegant solution to my problem. Instead of looping through the row, you re-wrote its values as properties of an entire row. Clever. – Jamie Marshall Oct 14 '14 at 20:18

Broken VBA Loop

1 Answers1