Please help!!!!(merge a number of rows)

  • Thread starter Thread starter Smeesh
  • Start date Start date
S

Smeesh

Hello All,

I have a worksheet with a number of columns that I want to merge, but I want
to keep them in rows. I know how to do one row at a time (highlight the
columns, click the merge button), but as I have several rows this will take
me forever.

I have tried with a macro, but it did not work as expected.

I would appreciate any help to save me time!!!

Regards

Meesha
 
Hi
this can only be done with VBA. Try the following macro (which merges
the selection row by row). Note: Most peopale would recommend not to
use merged cells as they will create problems if you want to use
filteriugn, sorting, etc.

Sub merge_cells()
Dim ret_str
Dim rng As Range
Dim row_index as long
dim column_index as long
Set rng = Selection

for col_index = rng.column to rng.columns.count+rng.column-1
ret_str=""
for row_index=rng.row to rng.rows.count+rng.row-1
if cells(row_index,col_index).value<>"" then
If ret_str = "" Then
ret_str = cells(row_index,col_index).value
Else
ret_str = ret_str & " - " &
cells(row_index,col_index).value
End If
End If
next row_index
Application.DisplayAlerts = False
With range(cells(row_index,rng.column),cells(col_index,
rng.columns.count+rng.column-1))
.MergeCells = True
.Value = ret_str
End With
Application.DisplayAlerts = True
next col_index
End Sub
 
Back
Top