parse Excel

  • Thread starter Thread starter George
  • Start date Start date
G

George

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.
 
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.
 
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.
 
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)


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.
 
Rick, thank you very much, it works! :)

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)


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.
Click to expand...
 
Back
Top