parse Excel
 

I've read your question several times and I am still not completely clear as
to what you want to do. Perhaps the problem is your example which seems to
show mostly empty data in Column F. The best I can figure from your example
is you will only be data that occurs immediately following an (empty)(empty)
F/J column entry... is that right, or will there be occasions when Column F
has non-empty entries that do not immediately follow an (empty)(empty) F/J
column entry? Perhaps if you give us a more general example AND show us what
you want to place on your other sheet, that might help us be able to help
you better.

--
Rick (MVP - Excel)


"George" wrote in message
...
Dear group members,

I have got .xls file where two columns (F and J) must be parsed.
Columns look like this:

F J
aaa bbb
(empty) ccc
(empty) ddd
(empty) (empty)
fff ggg
(empty) rrr
(empty) vvv
(empty) (empty)


So, I need to go through these columns and copy data to other sheet:
column J - "as is"
column F - all but empty cells; copy and paste the same data from
column F down untill we reach two empty cells in columns F and J;
after that we copy and paste the next not empty data from column F.


I have to parse 23000 rows.


Help me please, I'm new to VBA.

parse Excel
 

Ok, I'll try to describe it better.

I have columns like

F J
data1 data2
(empty) data3
(empty) data4
(empty) (empty)
data5 data6
(empty) data7
(empty) data8
(empty) data9
(empty) (empty)

My result must look like:

F J
data1 data2
data1 data3
data1 data4
(empty) (empty)
data5 data6
data5 data7
data5 data8
data5 data9
(empty) (empty)

I hope it is better to understand now...
I have to parse 23000 rows.

On 29 ôÒÁ, 17:59, "Rick Rothstein"
wrote:
I've read your question several times and I am still not completely clear as
to what you want to do. Perhaps the problem is your example which seems to
show mostly empty data in Column F. The best I can figure from your example
is you will only be data that occurs immediately following an (empty)(empty)
F/J column entry... is that right, or will there be occasions when Column F
has non-empty entries that do not immediately follow an (empty)(empty) F/J
column entry? Perhaps if you give us a more general example AND show us what
you want to place on your other sheet, that might help us be able to help
you better.

--
Rick (MVP - Excel)

"George" wrote in message

...

Dear group members,

I have got .xls file where two columns (F and J) must be parsed.
Columns look like this:

F J
aaa bbb
(empty) ccc
(empty) ddd
(empty) (empty)
fff ggg
(empty) rrr
(empty) vvv
(empty) (empty)

So, I need to go through these columns and copy data to other sheet:
column J - "as is"
column F - all but empty cells; copy and paste the same data from
column F down untill we reach two empty cells in columns F and J;
after that we copy and paste the next not empty data from column F.

I have to parse 23000 rows.

Help me please, I'm new to VBA.

parse Excel
 

Give the following macro a try, just set the DataStartRow, Source and
Destination constants (on the lines starting with Const) to values that
match your actual setup)...

Sub CopyExtendColumnsFandJ()
Dim X As Long
Dim Frow As Long
Dim LastDataRow As Long
Const DataStartRow As Long = 2
Const Source As String = "Sheet2"
Const Destination As String = "Sheet3"
With Worksheets(Source)
LastDataRow = .Cells(.Rows.Count, "J").End(xlUp).Row
Frow = DataStartRow
For X = DataStartRow To LastDataRow
If .Cells(X, "J").Value < "" Then
If .Cells(X, "F").Value = "" Then
Worksheets(Destination).Cells(X, "F").Value = .Cells(Frow,
"F").Value
Else
Frow = X
Worksheets(Destination).Cells(X, "F").Value = .Cells(X, "F").Value
End If
Worksheets(Destination).Cells(X, "J").Value = .Cells(X, "J").Value
Else
Frow = X
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"George" wrote in message
...
Ok, I'll try to describe it better.

I have columns like

F J
data1 data2
(empty) data3
(empty) data4
(empty) (empty)
data5 data6
(empty) data7
(empty) data8
(empty) data9
(empty) (empty)

My result must look like:

F J
data1 data2
data1 data3
data1 data4
(empty) (empty)
data5 data6
data5 data7
data5 data8
data5 data9
(empty) (empty)

I hope it is better to understand now...
I have to parse 23000 rows.

On 29 ôÒÁ, 17:59, "Rick Rothstein"
wrote:
I've read your question several times and I am still not completely clear
as
to what you want to do. Perhaps the problem is your example which seems to
show mostly empty data in Column F. The best I can figure from your
example
is you will only be data that occurs immediately following an
(empty)(empty)
F/J column entry... is that right, or will there be occasions when Column
F
has non-empty entries that do not immediately follow an (empty)(empty) F/J
column entry? Perhaps if you give us a more general example AND show us
what
you want to place on your other sheet, that might help us be able to help
you better.

--
Rick (MVP - Excel)

"George" wrote in message

...

Dear group members,

I have got .xls file where two columns (F and J) must be parsed.
Columns look like this:

F J
aaa bbb
(empty) ccc
(empty) ddd
(empty) (empty)
fff ggg
(empty) rrr
(empty) vvv
(empty) (empty)

So, I need to go through these columns and copy data to other sheet:
column J - "as is"
column F - all but empty cells; copy and paste the same data from
column F down untill we reach two empty cells in columns F and J;
after that we copy and paste the next not empty data from column F.

I have to parse 23000 rows.

Help me please, I'm new to VBA.

parse Excel
 

Rick, thank you very much, it works! :)

On May 29, 10:25*pm, "Rick Rothstein"
wrote:
Give the following macro a try, just set the DataStartRow, Source and
Destination constants (on the lines starting with Const) to values that
match your actual setup)...

Sub CopyExtendColumnsFandJ()
* Dim X As Long
* Dim Frow As Long
* Dim LastDataRow As Long
* Const DataStartRow As Long = 2
* Const Source As String = "Sheet2"
* Const Destination As String = "Sheet3"
* With Worksheets(Source)
* * LastDataRow = .Cells(.Rows.Count, "J").End(xlUp).Row
* * Frow = DataStartRow
* * For X = DataStartRow To LastDataRow
* * * If .Cells(X, "J").Value < "" Then
* * * * If .Cells(X, "F").Value = "" Then
* * * * * Worksheets(Destination).Cells(X, "F").Value = .Cells(Frow,
"F").Value
* * * * Else
* * * * * Frow = X
* * * * * Worksheets(Destination).Cells(X, "F").Value = .Cells(X, "F").Value
* * * * End If
* * * * Worksheets(Destination).Cells(X, "J").Value = .Cells(X, "J").Value
* * * Else
* * * * Frow = X
* * * End If
* * Next
* End With
End Sub

--
Rick (MVP - Excel)

"George" wrote in message

...
Ok, I'll try to describe it better.

I have columns like

F * * * * * * * J
data1 * * * data2
(empty) * *data3
(empty) * *data4
(empty) * *(empty)
data5 * * * data6
(empty) * *data7
(empty) * *data8
(empty) * *data9
(empty) * *(empty)

My result must look like:

F * * * * * * * J
data1 * * * data2
data1 * * * data3
data1 * * * data4
(empty) * *(empty)
data5 * * * data6
data5 * * * data7
data5 * * * data8
data5 * * * data9
(empty) * *(empty)

I hope it is better to understand now...
I have to parse 23000 rows.

On 29 ôÒÁ, 17:59, "Rick Rothstein"

wrote:
I've read your question several times and I am still not completely clear
as
to what you want to do. Perhaps the problem is your example which seems to
show mostly empty data in Column F. *The best I can figure from your
example
is you will only be data that occurs immediately following an
(empty)(empty)
F/J column entry... is that right, or will there be occasions when Column
F
has non-empty entries that do not immediately follow an (empty)(empty) F/J
column entry? Perhaps if you give us a more general example AND show us
what
you want to place on your other sheet, that might help us be able to help
you better.

--
Rick (MVP - Excel)

"George" wrote in message

...

Dear group members,

I have got .xls file where two columns (F and J) must be parsed.
Columns look like this:

F J
aaa bbb
(empty) ccc
(empty) ddd
(empty) (empty)
fff ggg
(empty) rrr
(empty) vvv
(empty) (empty)

So, I need to go through these columns and copy data to other sheet:
column J - "as is"
column F - all but empty cells; copy and paste the same data from
column F down untill we reach two empty cells in columns F and J;
after that we copy and paste the next not empty data from column F.

I have to parse 23000 rows.

Help me please, I'm new to VBA.