VBA - Offset to the next line and loop

Hi There

I am trying to teach myself some basics in VBA/ Macro's.

I have recorded the macro but can not get the thing to move to the next row and loop it to either a blank cell or after X amount of occasions.

Pretty basic stuff for you guys but.....

Here is my macro to date.

Range("D34:F34").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("G34:I34").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("J34:M34").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("N34:O34").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("P34:Q34").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("R34:S34").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("R34:T34").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("U34:W34").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
Range("X34:AH34").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
End Sub

Thanks in advance

Originally Posted by Norie

That's a lot of code, could you maybe wrap it in code tags to make it a bit easier to read and copy?

To add code tags, select the code and click the # button in the toolbar above the post window.

Erm looking into that too but I have just swapped to 2010 and still getting use to it

but it basically is to merge cells heres a simpler version that I would like to loop


Range("D35:F35").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.merge
End Sub

I can't really tell what you are doing in your code, but if I am understanding you correctly, this should work.

Please Login or Register  to view this content.

If you could explain what you are trying to do, might be easier for me to come up with a code.

Originally Posted by dkim.ags

I can't really tell what you are doing in your code, but if I am understanding you correctly, this should work.

Please Login or Register  to view this content.

If you could explain what you are trying to do, might be easier for me to come up with a code.

Hi All I am trying to do is merge 3 cells together. D18 D19 & D20 but going down to a D1000 or so.

I have recorded the macro rather than writing it and thats why there is probably so many command lines.

I can get it to work on one line but can not get it to step down a row - complete the merge and then step down again.

This is probably so simple but I may be runnibg before i can walk with VBA's

hi

You should not be using a loop for this at all: Copy and paste special is all you need. Very Fast.

Please Login or Register  to view this content.

However if you need to use a loop then the following will work as well try it and see how long it takes:

Please Login or Register  to view this content.

Hi

Thanks for the info

I am just playing with it now - its just I have different values in the cells so I did want to just merge them but I have just copied the data back into them.

Thanks for your help

Originally Posted by Norie

The original code isn't just merging 3 cells.

It merges D34:F34, G34:I34 ,J34:M34... etc.

So that's a whole bunch of merging happening.

Yeah I was trying to be far too clever - I need all the cells merging but I have decided to do it one col at a time - getting round the problem and not answering it I know...