PDA

View Full Version : Solved: merge blank record in first column



cruise
06-03-2012, 03:23 AM
In the excel sheet, I want to merge all blank record for column A. Last row word should always be in bold letter and the row will be in "all borders" until it encounter any word
Please note that rows are in dynamic range
In the attached sheet, I want output to be in "sheet2" from "sheet1"

cruise
06-03-2012, 07:28 AM
Whenever I am doing merging for some rows using macro recording, I am getting output like this. How can I do for all records.

Please run the macro inside excel for your reference

Bob Phillips
06-03-2012, 08:16 AM
Sub ProcessData()
Dim cell As Range
Dim lastrow As Long
Dim startrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 3 To lastrow - 1

startrow = i
Do Until .Cells(i, "A").Interior.ColorIndex = 48

i = i + 1
Loop

With .Cells(startrow, "A").Resize(i - startrow)

.Merge
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Next i

.Rows(i).Font.Bold = True
End With
End Sub

cruise
06-03-2012, 08:54 AM
Thanks Cruise for your great help.
But the last row is not getting "All bordered"where "Grand total" is there, Could you please check

Advance many many thanks

cruise
06-03-2012, 09:18 AM
Thanks Xld, I am getting output. Sorry to trouble you. I forgot to tell you one thing. In that sheet, whereever I will encounter the word "total", I want to put that row as "all bordered". Please help me