Lösch von zu vielen oder zu wenigen Daten
03.08.2015 15:23:50
zu
Ich habe nun ein neues naja "Problem".
Nun Arbeite ich schon seit längerem an einem Makro, das mir eine Datei die ich importiere umstellt sodas es dann sofort als csv gespeichert und Importiert werden kann in ein anderes System.
Folgendes Problem:
Ein teil meines makros löscht entweder zu viel oder zu wenig sobald ich nicht mehr über 100000 Zeilen habe. Hier einmal der Teil meines Makros:
Range("A1").Select
ActiveCell.FormulaR1C1 = "AD_Org_ID[Name]"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Value"
Range("C1").Select
ActiveCell.FormulaR1C1 = "VersionNo"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Help"
Range("G1").Select
ActiveCell.FormulaR1C1 = "DocumentNote"
Range("H1").Select
ActiveCell.FormulaR1C1 = "UPC"
Range("I1").Select
ActiveCell.FormulaR1C1 = "SKU"
Range("J1").Select
ActiveCell.FormulaR1C1 = "IsActive"
Range("K1").Select
ActiveCell.FormulaR1C1 = "IsSummary"
Range("L1").Select
ActiveCell.FormulaR1C1 = "M_Product_Category_ID[Value]"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Classification"
Range("N1").Select
ActiveCell.FormulaR1C1 = "C_TaxCategory_ID[Name]"
Range("O1").Select
ActiveCell.FormulaR1C1 = "C_UOM_ID[Name]"
Range("P1").Select
ActiveCell.FormulaR1C1 = "SalesRep_ID[Name]"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "ProductType[Name]"
Range("R1").Select
ActiveCell.FormulaR1C1 = "R_MailText_ID[Name]"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Weight"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Volume"
Range("U1").Select
ActiveCell.FormulaR1C1 = "IsOwnBox"
Range("V1").Select
ActiveCell.FormulaR1C1 = "M_FreightCategory_ID[Value]"
Range("W1").Select
ActiveCell.FormulaR1C1 = "IsDropShip"
Range("X1").Select
ActiveCell.FormulaR1C1 = "IsStocked"
Range("Y1").Select
ActiveCell.FormulaR1C1 = "M_Locator_ID"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "IsManufactured"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "IsPhantom"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "ShelfWidth"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "IsKanban"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "ShelfHeight"
Range("AE1").Select
ActiveCell.FormulaR1C1 = "M_PartType_ID[Name]"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "ShelfDepth"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "UnitsPerPallet"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "IsBOM"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "IsInvoicePrintDetails"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "IsPickListPrintDetails"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "IsPurchased"
Range("AL1").Select
ActiveCell.FormulaR1C1 = "IsSold"
Range("AM1").Select
ActiveCell.FormulaR1C1 = "Discontinued"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "DiscontinuedAt"
Range("AO1").Select
ActiveCell.FormulaR1C1 = "IsExcludeAutoDelivery"
Range("AP1").Select
ActiveCell.FormulaR1C1 = "ImageURL"
Range("AQ1").Select
ActiveCell.FormulaR1C1 = "DescriptionURL"
Range("AR1").Select
ActiveCell.FormulaR1C1 = "GuaranteeDays"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "GuaranteeDaysMin"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "M_AttributeSet_ID[Name]"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "M_AttributeSetInstance_ID"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "IsWebStoreFeatured"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "IsSelfService"
Range("AX1").Select
ActiveCell.FormulaR1C1 = "Group1"
Range("AY1").Select
ActiveCell.FormulaR1C1 = "Group2"
Range("AZ1").Select
ActiveCell.FormulaR1C1 = "Group3"
Range("BA1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>M_Product_ID[Value]"
Range("BB1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>C_BPartner_ID[Value]"
Range("BC1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>QualityRating"
Range("BD1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>IsActive"
Range("BE1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>IsCurrentVendor"
Range("BF1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>UPC"
Range("BG1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>C_Currency_ID"
Range("BH1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>PriceList"
Range("BI1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>PriceEffective"
Range("BJ1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>PricePO"
Range("BK1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>RoyaltyAmt"
Range("BL1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>C_UOM_ID[Name]"
Range("BM1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>Order_Min"
Range("BN1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>Order_Pack"
Range("BO1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>DeliveryTime_Promised"
Range("BP1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>CostPerOrder"
Range("BQ1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>VendorProductNo"
Range("BR1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>VendorCategory"
Range("BS1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>Manufacturer"
Range("BT1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>Discontinued"
Range("BU1").Select
ActiveCell.FormulaR1C1 = "M_Product_PO>DiscontinuedAt"
Range("BV1").Select
ActiveCell.FormulaR1C1 = "M_ProductPrice>M_Product_ID[Value]"
Range("BW1").Select
ActiveCell.FormulaR1C1 = "M_ProductPrice>M_PriceList_Version_ID[Name]"
Range("BX1").Select
ActiveCell.FormulaR1C1 = "M_ProductPrice>IsActive"
Range("BY1").Select
ActiveCell.FormulaR1C1 = "M_ProductPrice>PriceList"
Range("BZ1").Select
ActiveCell.FormulaR1C1 = "M_ProductPrice>PriceStd"
Range("CA1").Select
ActiveCell.FormulaR1C1 = "M_ProductPrice>PriceLimit"
'Löschen
Dim ALastrow As Long
ALastrow = Range("F" & Rows.Count).End(xlUp).Row
Range("F2:G" & ALastrow).Clear
Dim BLastrow As Long
BLastrow = Range("J" & Rows.Count).End(xlUp).Row
Range("J2:N" & BLastrow).Clear
Dim CLastrow As Long
CLastrow = Range("R" & Rows.Count).End(xlUp).Row
Range("R2:AA" & CLastrow).Clear
Dim DLastrow As Long
DLastrow = Range("AD" & Rows.Count).End(xlUp).Row
Range("AD2:AI" & DLastrow).Clear
Dim ELastrow As Long
ELastrow = Range("AK" & Rows.Count).End(xlUp).Row
Range("AK2:AZ" & ELastrow).Clear
'Verschieben
Dim ASelectingcells As Long
ASelectingcells = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & ASelectingcells).Select
Selection.Cut Destination:=Range("AX2")
Dim BSelectingcells As Long
BSelectingcells = Range("B" & Rows.Count).End(xlUp).Row
Range("B2:B" & BSelectingcells).Select
Selection.Cut Destination:=Range("AY2")
Dim CSelectingcells As Long
CSelectingcells = Range("C" & Rows.Count).End(xlUp).Row
Range("C2:C" & CSelectingcells).Select
Selection.Cut Destination:=Range("AZ2")
Dim DSelectingcells As Long
DSelectingcells = Range("D" & Rows.Count).End(xlUp).Row
Range("D2:D" & DSelectingcells).Select
Selection.Cut Destination:=Range("BS2")
Dim ESelectingcells As Long
ESelectingcells = Range("H" & Rows.Count).End(xlUp).Row
Range("H2:H" & ESelectingcells).Select
Selection.Cut Destination:=Range("D2")
Dim GSelectingcells As Long
GSelectingcells = Range("I" & Rows.Count).End(xlUp).Row
Range("I2:I" & GSelectingcells).Select
Selection.Cut Destination:=Range("E2")
Dim HSelectingcells As Long
HSelectingcells = Range("G" & Rows.Count).End(xlUp).Row
Range("G2:G" & HSelectingcells).Select
Selection.Cut Destination:=Range("I2")
Dim ISelectingcells As Long
ISelectingcells = Range("O" & Rows.Count).End(xlUp).Row
Range("O2:O" & ISelectingcells).Select
Selection.Cut Destination:=Range("AR2")
Dim JSelectingcells As Long
JSelectingcells = Range("Q" & Rows.Count).End(xlUp).Row
Range("Q2:Q" & JSelectingcells).Select
Selection.Cut Destination:=Range("BJ2")
Dim KSelectingcells As Long
KSelectingcells = Range("AB" & Rows.Count).End(xlUp).Row
Range("AB2:AB" & KSelectingcells).Select
Selection.Cut Destination:=Range("AQ2")
Dim LSelectingcells As Long
LSelectingcells = Range("AC" & Rows.Count).End(xlUp).Row
Range("AC2:AC" & LSelectingcells).Select
Selection.Cut Destination:=Range("AP2")
Dim MSelectingcells As Long
MSelectingcells = Range("AJ" & Rows.Count).End(xlUp).Row
Range("AJ2:AJ" & MSelectingcells).Select
Selection.Cut Destination:=Range("S2")
Dim NSelectingcells As Long
NSelectingcells = Range("P" & Rows.Count).End(xlUp).Row
Range("P2:P" & NSelectingcells).Select
Selection.Cut Destination:=Range("BZ2")
Er löscht mir folgende zeilen zuviel weg oder zu wenig oder erstellt einfach die Felder nicht.
F1,G1,J1,K1,L1,M1,N1,AD1,AE1,AF1,AG1,AH1,AI1 Werden anscheinend nicht erstellt oder mitgelöscht.
K3, L3, AE3, AG3, AH3, AI3, alle bis zur letzten befüllten Zeile nicht gelöscht worden
Komme leider nicht drauf warum er bei wenigen zeilen diesen Fehler macht aber bei übermässig vielen (also allen) alles richtig macht.
Hoffe wiedermal auf eure Hilfe!
Bei unklarheiten einfach melden werde so früh wie möglich dann melden.
LG
Patrick