04.08.2017, 12:15
I have a problem with a table, consisting of one column with data. The data is like
Product1 Information1 (next line) Information2 (next line)
Product 2
Product 3 Informrmation1 (next line) Information2 (next line) Information3 (next line)
As you can see there are a different number of informationlines in each cell. All I want is the same table, but only with the first line of each Cell.... I tried a combination of the a LEFT and FIND function but the result was not good enaugh.
The table is created with a VBA code using the advanced filter option on another table in a different worksheet.
If I use formulas like =LEFT(A9;FIND(CHAR(10);A9)-1) there will of course be an error on cells with only 1 line....
I'd love an idea on how to get my problem solved and every help is very appreciated:)
PS: I'd prefer a method I can include in the creation of the table (the code displayed above) if thats possible. If someone has other methods to get this over with Ill also be happy to use them.
Kind regards
hellownero
Product1 Information1 (next line) Information2 (next line)
Product 2
Product 3 Informrmation1 (next line) Information2 (next line) Information3 (next line)
As you can see there are a different number of informationlines in each cell. All I want is the same table, but only with the first line of each Cell.... I tried a combination of the a LEFT and FIND function but the result was not good enaugh.
The table is created with a VBA code using the advanced filter option on another table in a different worksheet.
Code:
Sub Advanced_Filter()
Worksheets("Tabelle1").Range("A8:A30").Clear
Sheets("Hirata Bestellformular").Range("Tabelle3[[#Headers],[#Data]]"). _
AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets( _
"Hirata Bestellformular").Range("I6:I7"), CopyToRange:=Range("A7:A8"), _
Unique:=False
End SubIf I use formulas like =LEFT(A9;FIND(CHAR(10);A9)-1) there will of course be an error on cells with only 1 line....
I'd love an idea on how to get my problem solved and every help is very appreciated:)
PS: I'd prefer a method I can include in the creation of the table (the code displayed above) if thats possible. If someone has other methods to get this over with Ill also be happy to use them.
Kind regards
hellownero