parse Excel

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

 

[Rick Rothstein]

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.

 

[George]

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 Rothstein]

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.

 

[George]

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.