Merge cells selectively?

[SouthAfricanStan]

Win XP, Office 2003
I have a worksheet containing +- 900 rows.
How do I merge cells in cols A to F ONLY in rows 1, 6, 11 and so on.
In col A there are text values rows 1, 6, 11 and so on, the other rows, in
col A, are blank
All other rows to be left as they were

 

[Dave Peterson]

Merged cells can cause lots of problems later on--sorting, filtering,
copy|pasting...

You may want to consider center across selection instead.

But if you really need them, you could use a macro:

Option Explicit
Sub testme()
Dim iRow As Long
Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyCols As Long

Set wks = ActiveSheet

With wks
HowManyCols = 6 'A:F is 6 columns
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'or 900
Application.DisplayAlerts = False
For iRow = 1 To LastRow Step 5
.Cells(iRow, "A").Resize(1, HowManyCols).Merge
Next iRow
Application.DisplayAlerts = True
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

I see in your next message that you're deleting rows with empty cells in a
certain column.

If that leaves nothing but the rows that should be merged, you can merge them in
one step:

Option Explicit
Sub testme2()
Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyCols As Long

Set wks = ActiveSheet

With wks
HowManyCols = 6 'A:F is 6 columns
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'or 900
Application.DisplayAlerts = False
.Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")) _
.Resize(, HowManyCols).Merge across:=True
Application.DisplayAlerts = True
End With
End Sub