excel - Broken VBA Loop -

excel - Broken VBA Loop -

i'm sure simple can't find on web.

i'm writing macro format xl spreadsheets download 3rd party application. come formatted wacky i'm trying create easier info need them.

this simple vba loop causes cells in column bl update. info in these cells contain line breaks don't show until double click in cell. vba below causes update cells achieves same effect, less work. crashing excel , can't figure out why. works in single instance, when loop -- boom!!! -- frozen. help gently appreciated.

sub updatecell() dim currentvalue string activesheet.range("bl1").select until activecell.value = "" activecell.offset(1, 0).select currentvalue = activecell().value activecell().value = currentvalue & "" loop end sub

try bit more direct:

with activesheet lrow = .range("bl" & .rows.count).end(xlup).row '~~> find lastly row on bl .range("bl1:bl" & lrow) '~~> work on target range .value = .value '~~> assign current value end end

above code manually pressing f2 pressing enter.

edit1: explanation on getting lastly row

activesheet.rows.count '~~> returns number of rows in sheet 1048576 msgbox activesheet.rows.count '~~> run confirm

so line concatenates bl 1048576.

.range("bl" & .rows.count) '~~> count property of rows collection

same as:

.range("bl" & 1048576)

and same as:

.range("bl1048576")

then lastly row, utilize range object end method.

.range("bl" & .rows.count).end(xlup)

so basically, above code go cell bl1048576 manually pressing ctrl+arrow up. homecoming actual row number of range, utilize range object row property.

lrow = .range("bl" & .rows.count).end(xlup).row

see here more with statement. has same effect (with code) without loop. hth

but if want remove line breaks produced alt+enter on cell, seek below:

dim lrow long, c range activesheet lrow = .range("bl" & .rows.count).end(xlup).row each c in .range("bl1:bl" & lrow) c.value = replace(c.value, chr(10), "") next end

where chr(10) equivalent of line break replaced "" using replace function.

excel vba loops excel-vba