HI All,

Following is my requirement
-----------------------------------
Take an workbook (for ex: Develop.xls)
1) Develop.xls has 2000 records starting from row 3
2) each row has 30 columns. some columns has data/values; some doesnt have
values
3) a set of statements has ot be generated based on the cell values.

a set of statement should be created in such a way that, cell values should
get appended in teh next line of the statement ONLY if it has value

for ex:
Cell A =102234454, Cell B is blank, Cell C has a textvalue UNITEDBROTHERS

so, in another sheet result should be generated, leaving out the blank cells
102234454
UNITEDBROTHERS

-------

so, as i said a record has 30 columns. if a record has only 20 values out of
30 columns. in another sheet 20 values should be generated line-by-line
sequentially. ie. 20 lines will be there (if all the columns has values then
30 lines will be there)

In case, if only 20 values are there for a record; 10 lines should be
skipped before generating statements for the next record.

-------------------------------------------------------------------

As mentioned above, the sheet Develop wil contain 2000 to 3000 records wil
be there. and each record will contain 30 columsn(w/o data).

assuming all cells are filled with values. then, 2000 * 30 = 60000 rows has
to be filled in anothr sheet.


after generating statements, all statements / lines in that sheet has to be
copied
to a notepad(as a single file).

Please provide assistance to perform the above actions


Thanks & Regards,
Venkat

Here is some untested pseudo code that should get you started.
It assumes that there are no formulas in the worksheet to be processed and
that this worksheet is the first worksheet in the workbook. Data starting in
row 3 as you said. It further assumes that on the last row, there is data in
column A.

Sub writeData()
Dim fName As Variant, bk As Workbook
Dim cell As Range, cell1 As Range
Dim rng As Range, rng1 As Range
Dim rng2 As Range, sh As Worksheet
Dim i As Long
fName = Application.GetOpenFilename()
If fName = False Then Exit Sub
Set bk = Workbooks.Open(fName)
With bk.Worksheets(1)
.Copy
Set sh = ActiveSheet
Set rng = .Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
i = 1
For Each cell In rng
Set rng1 = cell.Resize(1, Columns.Count)
Set rng2 = Nothing
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlConstants)
On Error GoTo 0
For Each cell1 In rng2
sh.Cells(i, 1) = cell1
i = i + 1
Next
Next

fName = Application.GetSaveAsFilename()
If fName = False Then Exit Sub
ActiveSheet.Parent.SaveAs fName, xlTextMSDOS
ActiveWorkbook.Close SaveChanges:=False
bk.Close SaveChanges:=False
End Sub

--
Regards,
Tom Ogilvy


"Venkatesh V" wrote:

HI All,

Following is my requirement
-----------------------------------
Take an workbook (for ex: Develop.xls)
1) Develop.xls has 2000 records starting from row 3
2) each row has 30 columns. some columns has data/values; some doesnt have
values
3) a set of statements has ot be generated based on the cell values.

a set of statement should be created in such a way that, cell values should
get appended in teh next line of the statement ONLY if it has value

for ex:
Cell A =102234454, Cell B is blank, Cell C has a textvalue UNITEDBROTHERS

so, in another sheet result should be generated, leaving out the blank cells
102234454
UNITEDBROTHERS

-------

so, as i said a record has 30 columns. if a record has only 20 values out of
30 columns. in another sheet 20 values should be generated line-by-line
sequentially. ie. 20 lines will be there (if all the columns has values then
30 lines will be there)

In case, if only 20 values are there for a record; 10 lines should be
skipped before generating statements for the next record.

-------------------------------------------------------------------

As mentioned above, the sheet Develop wil contain 2000 to 3000 records wil
be there. and each record will contain 30 columsn(w/o data).

assuming all cells are filled with values. then, 2000 * 30 = 60000 rows has
to be filled in anothr sheet.


after generating statements, all statements / lines in that sheet has to be
copied
to a notepad(as a single file).

Please provide assistance to perform the above actions


Thanks & Regards,
Venkat