Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro for fixed number of rows
I have been sourcing for a solution but to
no avail. I seek assistance for a VBA code to be activated by macro selection. A2:AU4993 =4992 rows of 192 records of 26 rows each. I want to Copy R:AU of the 26th row of each record and PasteSpecialValue to R:AU of the 1st row. I would think the process should start from the last row and move upwards ie. R4993:AU4993 Copy and PastSepecialValue to R4968:AU4968. Then similar action from R4967:AU4967 to R4942:AU4942 and so until the first record R27:AU27 to R2:R27. Thank you. -- RobertR |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro for fixed number of rows
Hi,
Try this...it is untested but it should work. If not it will point you in the right direction... Option Explicit Sub LoopEm() Dim rngLoop as Range, rngLastRec as Range, rngFirstRec as Range, lngInc as Long Set rngLoop = thisworkbook.sheets(1).range("R2:AU4993") For lngInc = rngloop.rows.count to 2 step - 26 Set rngLastRec = rngloop.rows(lngInc) Set rngFirstRec = rngloop.rows(lngInc - 25) Let rngFirstRec.value = rngLastRec.value Next lngInc Set rngFirstRec = Nothing Set rngLastRec = Nothing End Sub Hth, Oli -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro for fixed number of rows
One way:
Dim i As Long For i = 2 To 4992 Step 26 Cells(i, 18).Resize(1, 30).Value = _ Cells(i + 25, 18).Resize(1, 30).Value Next i In article , Robert wrote: I have been sourcing for a solution but to no avail. I seek assistance for a VBA code to be activated by macro selection. A2:AU4993 =4992 rows of 192 records of 26 rows each. I want to Copy R:AU of the 26th row of each record and PasteSpecialValue to R:AU of the 1st row. I would think the process should start from the last row and move upwards ie. R4993:AU4993 Copy and PastSepecialValue to R4968:AU4968. Then similar action from R4967:AU4967 to R4942:AU4942 and so until the first record R27:AU27 to R2:R27. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro for fixed number of rows
Dear McGimpsey
Your "One way" works for me (though slow,not your fault) Thank you very much. Regards, RobertR |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro for fixed number of rows
Hmmm... In my test workbook, it takes about 80 milliseconds to cycle
through the whole record set. If you have a lot of calculations, bracket your code with this: Dim oldCalc As Long With Application .ScreenUpdating = False oldCalc = .Calculation .Calculation = xlManual End With '...code here With Application .Calculation = oldCalc .Screenupdating = true End With In article , Robert wrote: Your "One way" works for me (though slow,not your fault) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro for fixed number of rows
Dear McGimpsey Before seeing your second posting I did what you are suggesting i.e. freeze calculations. Yes it ran very very very fast indeed. I shall now include your additional codes. Once again thank you very much. I really appreciate your asssitance. RobertR. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Utilizing a macro for a datasets that don't have a fixed number of | Excel Discussion (Misc queries) | |||
How do I add rows at fixed intervals-add values after every 5 Rows | Excel Discussion (Misc queries) | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
making a cell fixed number to a input number | Excel Programming |