Prev: Opening a .doc from code
Next: Fieldcode IF
From: ng6971 on 24 Mar 2010 16:28
Hi All,

I have a table with 3 columns in following manner:

C1 C2 C3
Blank Blank Value
Value Value Value

and so on.

1. I just want that merge all blank cells of column 1 and
blank cells of column 2.

2. If there is value in column 1 insert "WW" at beginning of cell and
in column 2 if value finds insert "VV" at begining of cell and then
merge both of cells.

Thanks in advance.
From: Jef Gorbach on 25 Mar 2010 14:13
On Mar 24, 4:28 pm, ng6971 <ng6...(a)discussions.microsoft.com> wrote:
> Hi All,
>
> I have a table with 3 columns in following manner:
>
> C1           C2           C3
> Blank        Blank       Value
> Value        Value      Value
>
> and so on.
>
> 1.   I just want that merge all blank cells of column 1 and
>       blank cells of column 2.
>
> 2.   If there is value in column 1 insert "WW" at beginning of cell and
>      in column 2 if value finds insert "VV" at begining of cell and then
>      merge both of cells.
>
> Thanks in advance.


Sub Macro1()
'process column 1
For Each c In Range("A2:A65536").SpecialCells(xlTextValues)
c.Value = Trim("WW " & c.Value)
Next
'process column 2
For Each c In Range("b2:b65536").SpecialCells(xlTextValues)
c.Value = Trim("W " & c.Value)
Next

'I suspect you mean to concante c1 and c2?
For Row = 2 To Range("A65536").End(xlUp).Row
Cells(Row, 6).Value = Trim(Cells(Row, 1).Value & " " & Cells(Row,
2).Value)
Next

'because merging them only keeps the leftmost value if both columns
have data.
For Row = 2 To Range("A65536").End(xlUp).Row
Range(Cells(Row, 1), Cells(Row, 2)).MergeCells = True
Next
End Sub
 | 
Pages: 1
Prev: Opening a .doc from code
Next: Fieldcode IF