| Home |
| Search |
| Today's Posts |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining VBA code for columns
Tom Ogilvy wrote: i should have been 8. Thought I had corrected that. Thanks, Tom! -- Regards, Tom Ogilvy "davegb" wrote in message ups.com... Tom Ogilvy wrote: Figuring out what you are doing and rewriting it would be the way to improve your code, but to reduce the repetative blocks: for jj = 9 to 16 Cells(i,jj).Select Set rng = Range(ActiveCell.Address) ActiveCell.Offset(1, -7).Range("A1").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(0, 7).Range("A1").Select If ActiveCell.Value < "" Then l = ActiveCell.Value + l ActiveCell.Offset(1, -7).Select Else rng.Activate ActiveCell.Value = l Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Set rng = Range(ActiveCell.Address) ActiveCell.Offset(1, -7).Select l = 0 End If Loop rng.Activate ActiveCell.Value = l l = 0 Next jj -- Regards, Tom Ogilvy wrote in message oups.com... Hi- I have the following code that I run for 8 consecutive columns....columns I through P. As of now I am repeating this code 8 times (8 seperate blocks of code). This is obviously slowing the process down quite a bit. My question is this: Is there a way to combine all 8 columns into one block of code? Here is the code I am using: Range("I8").Select Set rng = Range(ActiveCell.Address) ActiveCell.Offset(1, -7).Range("A1").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(0, 7).Range("A1").Select If ActiveCell.Value < "" Then l = ActiveCell.Value + l ActiveCell.Offset(1, -7).Select Else rng.Activate ActiveCell.Value = l Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Set rng = Range(ActiveCell.Address) ActiveCell.Offset(1, -7).Select l = 0 End If Loop rng.Activate ActiveCell.Value = l l = 0 Then I have this code for column J.... Range("J8").Select Set rng = Range(ActiveCell.Address) ActiveCell.Offset(1, -8).Range("A1").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(0, 8).Range("A1").Select If ActiveCell.Value < "" Then l = ActiveCell.Value + l ActiveCell.Offset(1, -8).Select Else rng.Activate ActiveCell.Value = l Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Set rng = Range(ActiveCell.Address) ActiveCell.Offset(1, -8).Select l = 0 End If Loop rng.Activate ActiveCell.Value = l l = 0 TIA, Chris I have a question on this one. What is "i"? Since it's not defined anywhere, does XL default to some value? |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combining two columns together | Excel Discussion (Misc queries) | |||
| combining columns | Excel Discussion (Misc queries) | |||
| Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
| combining columns all the way down | Excel Discussion (Misc queries) | |||
| combining 2+ wkbks into 1. Code needs tweaking please | Excel Programming | |||
Threaded Mode
