Read Part of Excel Based on Certain Column Value. Split One major Excel to Two parts

[Hurmet_Noka]

Hello Guys.
I Hope you are well.

I have this excel sheet and I want to split it on two excel Sheets or Two DataTables.
First Should Contain Everything Up to The Yellow Lines without including them, meaning a certain row will be emtpy.

Second File will Read below the yellow lines until the next yellow lines where the rows are still emtpy.

Please guys advice.
Should I use a for each row, and copy the row to another data table if the particular column value is not empty?

Thank You
Hurmet Noka

[Jeroen]

Hi @Hurmet_Noka,
Yes, I think you are right. See example below. You could use a counter to name the datatables/excelfiles dynamically, because you probably won’t know in advance how many you will need…

IF column B is empty
THEN add row to Datatable2
ELSE
IF datatable2.rows.count >0
THEN Write Range, Clear Datatable2
ELSE Continue

[ermanoj3101 (MANOJ)]

Hi @Hurmet_Noka ,

You can achieve this with macro also.

1 - Get Row Number of highlighted row and store it in variable
2 - Now use Read range one for range “A1:D” + (lastHighlitedRow -2)
3 - For Second table use range as “A” + (lastHighlitedRow + 2) + “D” + lastUsedRow

I have shown you to get row number for column A, similar way you can also get last row number of entire sheet.

[lukasziebold]

Hi Hurmet,

I would probably:

• Iterate through the full DataTable with For Each Row and declare a variable for the RowIndex
• Get Cell Color with a range based on the RowIndex (careful, RowIndex 0 is usually row 2 in Excel)
• If the color is yellow, use that RowIndex to copy your rows to a new DataTable like this: NewDataTable = FullDataTable.AsEnumerable.Skip(PreviousIndex).Take(CurrentIndex - PreviousIndex + 1).CopyToDataTable
• Put the CurrentIndex as the PreviousIndex (+1) after and you can skip the first part that you already saved somewhere and take the second part of rows from the PreviousIndex to the CurrentIndex

Hope this helps

[Hurmet_Noka]

Bless you! Saved my day