Hi Tom Ogilvy,

No, what I want is to stop processing if column K is "NOT" empty.

Anyway please kindly take a look at the module below.

The module start from the last row, read "Col D & Col E" then fill the
values into "Col K" to "Col R".

I need to add in one condition i.e. whenever it encounters the first
"Col K" is "NOT EMPTY" then "EXIT THE SUB" otherwise continue to
process until the first "Col K" is "NOT EMPTY". Many rows of datas are
add in everyday to "Col D & Col E". So to speed up the updating process
I just need to update "Col K" to "Col R" that have not been updated
instead of running the code from the last row until the first row for
every execution.

I hope I have clearly explained. Please kindly add in or modify the
code.

SUB MOVEDATA()

DIM MYRANGE AS RANGE
DIM MYROW AS LONG
DIM I AS INTEGER
DIM J AS INTEGER
DIM MYCNT AS INTEGER

WITH ACTIVESHEET.USEDRANGE
MYROW = .ROWS(.ROWS.COUNT).ROW
END WITH

WHILE MYROW 0
MYCNT = 0
SET MYRANGE = RANGE(\"D\" & MYROW & \":E\" & MYROW)
FOR I = 1 TO -MYROW + 2 STEP -1
FOR J = 1 TO 2
IF APPLICATION.COUNTIF(RANGE(\"K\" & MYROW & \":R\" & MYROW), _
MYRANGE.CELLS(I, J).VALUE) = 0 THEN
IF ISEMPTY(RANGE(\"K\" & MYROW)) THEN
RANGE(\"K\" & MYROW).VALUE = MYRANGE.CELLS(I, J).VALUE
RANGE(\"L\" & MYROW).RESIZE(1, 7).CLEARCONTENTS
ELSE
RANGE(\"IV\" & MYROW).END(XLTOLEFT)(1, 2).VALUE = _
MYRANGE.CELLS(I, J).VALUE
END IF
MYCNT = MYCNT + 1
IF MYCNT = 8 THEN GOTO FOUND8:
END IF
NEXT J
NEXT I
FOUND8:
MYROW = MYROW - 1
WEND

END SUB


Regards,
Michael


---
Message posted from http://www.ExcelForum.com/