PDA

View Full Version : Reg : Merging of cells



vidya lakshm
09-08-2010, 11:14 PM
Hi,
I am a newbie in excel programming n need a solution for one of the problem.

Please refer the attachment and help me out with your solutions.


I need to merger the cells from Column F-J based on the no. of validations that a particular filetype(Column C) has.
Eg: doc has validation1-validation 5.So merger them.
Similarly xls has validation1-Validation3.So merge them
and so on.

Kindly help me in figuring out how this can be done at the earliest.

regards
Vidya

Simon Lloyd
09-09-2010, 12:02 AM
Firstly you can upload a workbook, adding a worksheet to a word document is pointless.

Secondly why merge the cells?, they will cause you so much problems in the future, you would be better of Centering text across a range of cells.

vidya lakshm
09-09-2010, 12:34 AM
Hi Simon,
Thanks for the suggestion. But am developing a small application for the learning purpose. I would like to know about the merging.

Attached is the spreadsheet for your reference.
Would be of great help if u could guide m thru it.

Bob Phillips
09-09-2010, 12:34 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<<<< change to suit
Dim Lastrow As Long
Dim LastCol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To Lastrow

If .Cells(i, "B").Value2 <> "" Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
Do

i = i + 1
If .Cells(i, "B").Value2 = "" Then .Cells(i, "F").Resize(, LastCol - 5).Merge
Loop Until .Cells(i, TEST_COLUMN).Value2 = "" Or i > Lastrow
i = i - 1
End If
Next i
End With

Application.ScreenUpdating = True
End Sub