Variable Range with a loop needed
 

Ok this is going to be long to explain.

Spreed Sheet has 7 columns and an unknown amount of rows as a total
so columns from A to G
and Rows = unknown

Colum definition

A = Date
B = Account #
C = Type of Account
D = Amount posted (on our system)
E = Amount posted (on an external system)
F = Date when it close
G = Total

now there is several different groups that use the same set up in the same
sheet
What I want to do is to make a macro that copy the value from E to G as follow
E.value = G.Value

The problem I'm having is that I do not have a specific range for each of
this group
I can add or delete rows as I see fit, but the macro needs to keep working
no matter what.

my answer was, I named the very first cell of the group (G1_First) using the
named range option in excel and the very last in that group named it
(G1_Last).

The macro I came up with was:
(Group 1)
For I = Range("G1_First").rows + 1 to Range("G1_Last").rows - 1
Cells(I,7).value = Cells(I,5).value
Next I

after doing this macro i went to the sheet and set it to run automaticaly in
the code of that sheet.

one macro per group 5 in total.

the problem I'm facing is that the loop seens to be very slow, even though,
the amount of rows are less than 1000.

Can anyone come up with a better code for this?

Variable Range with a loop needed
 

When I do this kind of stuff, I can usually depend on another column to have
data in it when that row is used -- for you, maybe the date or account number???

If that's ok with you, you could use something like this that actually does a
copy|paste special|values:

Option Explicit
Sub testme()
Dim LastRow As Long
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("E2:E" & LastRow)
myRng.Copy
.Range("G2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End Sub


I started in row 2 (E2 and G2) -- I figured headers in row 1. Change them if
you need to.

Alfredo wrote:

Ok this is going to be long to explain.

Spreed Sheet has 7 columns and an unknown amount of rows as a total
so columns from A to G
and Rows = unknown

Colum definition

A = Date
B = Account #
C = Type of Account
D = Amount posted (on our system)
E = Amount posted (on an external system)
F = Date when it close
G = Total

now there is several different groups that use the same set up in the same
sheet
What I want to do is to make a macro that copy the value from E to G as follow
E.value = G.Value

The problem I'm having is that I do not have a specific range for each of
this group
I can add or delete rows as I see fit, but the macro needs to keep working
no matter what.

my answer was, I named the very first cell of the group (G1_First) using the
named range option in excel and the very last in that group named it
(G1_Last).

The macro I came up with was:
(Group 1)
For I = Range("G1_First").rows + 1 to Range("G1_Last").rows - 1
Cells(I,7).value = Cells(I,5).value
Next I

after doing this macro i went to the sheet and set it to run automaticaly in
the code of that sheet.

one macro per group 5 in total.

the problem I'm facing is that the loop seens to be very slow, even though,
the amount of rows are less than 1000.

Can anyone come up with a better code for this?

--

Dave Peterson

Variable Range with a loop needed
 

Hi Alfredo

To me it looks like you made a small typo, try this:

For i = Range("G1_F").Row + 1 To Range("G1_L").Row - 1
Cells(i, 7).Value = Cells(i, 5).Value
Next i

HTH,

Wouter