Modify Recorded Macro

[Corey]

I recorded the following macro, which basically Merges 2 cells and Alignes
the text to the left.

However, when recording it always records the cell
address(Range("D******").Select.

How can i adapt the cell address to something like:

ActiveCell - ActiveCell.Offset(0,1).select instead of the particular cell
address.

I need to run this macro about 1000 times

*************************
Sub MergeCells()
'
' MergeCells Macro
'

'
Range("D156:E156").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("D156:E156").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("D157:E157").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("D158:E158").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
End Sub
********************


Corey....

 

[Dave Peterson]

Your code looks like it's really going down the D:E column--not across the row.

ActiveCell.Offset(0,1).select
would/should have been:
ActiveCell.Offset(1,0).select
Right???

If yes:

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
With .Range("D156").Resize(1000, 2) '1000 rows by 2 columns
.Merge across:=True 'each row is merged separately
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
End With
End With

End Sub

Notice that I did drop the ".mergecells = false" and ".mergecells = true" lines.