Do While loop

**JonathanMoore** · 09-12-2011, 10:35 AM

Hi Gang,

I'm a complete beginner to VBA loops. I've got an idea that I want to try out, but i'm unsure as to how to do it.

OK. I have the following code that selects a number of cells, then performs a paste-special command into a different sheet.

Please Login or Register  to view this content.

I need a loop that adds 1 to the first selected range (i.e. Range("A1:H1") becomes Range("A2:H2") and so on...) but also adds 8 to the second selected range (i.e. Range("A3") becomes Range("A11") etc etc). It should loop until the first Range ( Sheet1.Range("A1:H1")) reaches a blank cell.

Does anyone have any ideas about what I can do? As I said, i'm completely new to loops, but very willing to learn

Thanks in advance,

- Jon

**Kelshaer** · 09-12-2011, 10:39 AM

you have to tell when the loop will end or it will be an open one that will just keep on going forever

**snb** · 09-12-2011, 10:49 AM

Before that you can reduce your code to:

Please Login or Register  to view this content.

**JonathanMoore** · 09-12-2011, 03:57 PM

Thanks for the replies as ever!

The only problem with reducing my code to what SNB suggests is that I need to keep the paste special stuff so it transposes into a single column rather than the columns A to H. Or would modifying your code to:

Please Login or Register  to view this content.

work?

I would still need a loop that added 1 to my first range (i.e. A4:H4 and so on) and 8 to the second range (I.e. A9:A16 and so on).

@Kelshaer - is it possible to loop all the way to the bottom of the file and stop when it finds a blank cell? I can't specify how many rows it will have to loop through so I think it would be easier if it went through the entire document (as specified by the ranges quoted) and then stopped looping when it reaches a blank cell at the bottom.

Hope this is a little clearer,

thanks again,

- Jon

**Kelshaer** · 09-12-2011, 05:09 PM

here you go.
Please mark the thread as SOLVED if it is

Please Login or Register  to view this content.

**snb** · 09-12-2011, 05:56 PM

Avoid using Select & activate in VBA

Please Login or Register  to view this content.

**JonathanMoore** · 09-13-2011, 04:43 AM

Thanks guys, that all works brilliantly - thanks for all of your help, I think i'm starting to get my head around these loops.

I'll mark this thread as SOLVED now. thanks again!

- Jon