copy cells with a macro...

[John Keith]

I have added a logging feature to my excel project that keys off a button
press. When ever the button is clicked I want the data residing in a group
of cells to be copied (logged) to the next empty row in a "log" worksheet.

I have added some code that works, but it seems to be very inefficient.
What is a better(faster, more efficient) way to accomplish this...

(slow code)
' Button is set in a case stmt by reading the name of the button pressed (i
have
' 35 or so buttons on the 1st worksheet next to the BB## cells
' BH94 is always logged, BB95:BK95 is the row to log where Button is the
' row indicator (94+button)
v1 = Worksheets("Source").Range("BH94").Value
v2 = Worksheets("Source").Range("BB94").Offset(Button, 1).Value
v3 = Worksheets("Source").Range("BB94").Offset(Button, 2).Value
v4 = Worksheets("Source").Range("BB94").Offset(Button, 5).Value
v5 = Worksheets("Source").Range("BB94").Offset(Button, 4).Value
v6 = Worksheets("Source").Range("BB94").Offset(Button, 9).Value
v7 = Worksheets("Source").Range("BB94").Offset(Button, 8).Value

' find empty row in "log"; row(0) is the heading line in actual row1
' so intCLRow = 1 points to the 2nd row on the "log" sheet
Set x = Worksheets("Log").Range("A1").Cells
intCLRow = 0
Do While x > ""
intCLRow = intCLRow + 1
x = Worksheets("Log").Range("A1").Offset(intCLRow, 0).Value
Loop
'
' Record results into Log; intCLRow is set to the first empty row to fill
Worksheets("Log").Range("A1").Offset(intCLRow, 0).Value = v1
Worksheets("Log").Range("A1").Offset(intCLRow, 1).Value = v2
Worksheets("Log").Range("A1").Offset(intCLRow, 2).Value = v3
Worksheets("Log").Range("A1").Offset(intCLRow, 3).Value = v4
Worksheets("Log").Range("A1").Offset(intCLRow, 4).Value = v5
Worksheets("Log").Range("A1").Offset(intCLRow, 5).Value = v6
Worksheets("Log").Range("A1").Offset(intCLRow, 6).Value = v7

 

[Bernie Deitrick]

John,

One immediate way of speeding things up is to not loop to find the next
empty row. Replace:

' find empty row in "log"; row(0) is the heading line in actual row1
' so intCLRow = 1 points to the 2nd row on the "log" sheet
Set x = Worksheets("Log").Range("A1").Cells
intCLRow = 0
Do While x > ""
intCLRow = intCLRow + 1
x = Worksheets("Log").Range("A1").Offset(intCLRow, 0).Value
Loop

with

intCLRow = Worksheets("Log").Range("A65536").End(xlUp)(2).Row

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

John,

Sorry, I forgot to subtract 1 (Since you are using the offset method):

intCLRow = Worksheets("Log").Range("A65536").End(xlUp)(2).Row - 1

or simply

intCLRow = Worksheets("Log").Range("A65536").End(xlUp).Row

HTH,
Bernie
MS Excel MVP

John,

One immediate way of speeding things up is to not loop to find the next
empty row. Replace:

' find empty row in "log"; row(0) is the heading line in actual row1
' so intCLRow = 1 points to the 2nd row on the "log" sheet
Set x = Worksheets("Log").Range("A1").Cells
intCLRow = 0
Do While x > ""
intCLRow = intCLRow + 1
x = Worksheets("Log").Range("A1").Offset(intCLRow, 0).Value
Loop

with

intCLRow = Worksheets("Log").Range("A65536").End(xlUp)(2).Row

HTH,
Bernie
MS Excel MVP

 

[John Keith]

That line of code worked for me, I always like seeing different ways to code
things (one of these days I'll start to grasp more of this VB-syntax)

The run-time didnt change much though, the loop it replaced was only
itterating once of twice (since im still testing the logging feature) I bet
it would have made a huge difference if i was in the 1000s of rows logged
though.

Once thing I noticed, when I click one of the buttons, the "calculating
cells" message appears at the bottom left of the screen, I dont see the
percentage count upwards... it blinks at 0% then immediately goes to 100%,
which remains while the macro is completing. It takes about 4-5 seconds for
the process to finish.

 

[Bernie Deitrick]

John,

Any large workbook with lots of calc or other events will almost always
benefit by turning off auto-calc, screen updating, and events:

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Other code here

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

HTH,
Bernie
MS Excel MVP

 

[John Keith]

I have some other macros that will benifit greatly from using that code,
however this time I need calculation on, and maybe screen updating too. One
thing happening when the button is clicked (which i hadn't mentioned before)
a cell is updated that then has a table lookup which the result is then one
of the cells being logged.

I tried executing with some break points, both before and after, the source
assignments and target cells being updated. The assigments seem to happen
very fast. It's the updating of the target cells that takes all the
execution time. Is there a more concise way to write...

Worksheets("Log").Range("A1").Offset(intCLRow, 0).Value = v1
Worksheets("Log").Range("A1").Offset(intCLRow, 1).Value = v2
Worksheets("Log").Range("A1").Offset(intCLRow, 2).Value = v3

Im wondering if the Worksheets...Range... portion of these statements is
causing some extra overhead that I might could avoid.

Thanks for all the responces!
JK

 

[Bernie Deitrick]

John,

Each time you paste in a value, Excel will do a recalc: 7 pastes, 7 recalcs.
You need to decide if that is important or not.

It would also help if you could copy a block of cells to paste, so it may be
worthwhile setting up your sheet so that you can replace the cell by cell
stepping with a single copy/paste. For example, if your data were in a
block 7 columns wide, you could simply use:

Worksheets("Source").Range("BB94").Offset(Button, 1).Resize(1,7).Copy _
Worksheets("Log").Range("A65536").End(xlUp)(2)

HTH,
Bernie
MS Excel MVP