海外网站入口,服装网站建设的宗旨有哪些,加强网站建设考察交流,洛阳霞光只做有效果的网站源表格首列名称中一样的分别创建该名称的Sheet页#xff0c;首先把首列复制导致Sheet2页#xff0c;根据去重后的值创建新的Sheet页#xff0c;把源表格数据复制到新建的Sheet页#xff0c;遍历删除不需要的留下需要的就完成了。
function JIn521() { //设置工作…源表格首列名称中一样的分别创建该名称的Sheet页首先把首列复制导致Sheet2页根据去重后的值创建新的Sheet页把源表格数据复制到新建的Sheet页遍历删除不需要的留下需要的就完成了。
function JIn521() { //设置工作表对象 var ws1 ThisWorkbook.Worksheets(Sheet1); //Sheet1页首列复制到Sheet2页并去重 var lastRow ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row; var sheet1Range1 ws1.Range(ws1.Cells(1, 1), ws1.Cells(lastRow, 1)); Worksheets.Add(); ActiveSheet.Name Sheet2 var ws2 ThisWorkbook.Worksheets(Sheet2); sheet1Range1.Copy(ws2.Cells(1, 1)); ws2.Columns(1).EntireColumn.RemoveDuplicates(1, xlYes); //遍历Sheet2页值创建其余Sheet页 var st2lastRow ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row; var worksheetObjects {}; for(var z 0; zst2lastRow-1;z){ var sheet2Range2ItemValue ws2.Cells(z2, 1).Value2; var newWorksheet Worksheets.Add(); newWorksheet.Name sheet2Range2ItemValue; worksheetObjects[sheet2Range2ItemValue] newWorksheet; //Sheet1页复制到其他页 var lastCol ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column; var sheet1Range2 ws1.Range(ws1.Cells(1,1),ws1.Cells(lastRow,lastCol)); sheet1Range2.Copy(worksheetObjects[sheet2Range2ItemValue].Cells(1, 1)); //获取其他页数据及地址 var arrow new Array(); var st5LastRow worksheetObjects[sheet2Range2ItemValue].Cells(worksheetObjects[sheet2Range2ItemValue].Rows.Count, 1).End(xlUp).Row; for(var j 0 ; j st5LastRow ; j){ arrow[j]j1; } var arrow1 new Array(); for (let x 0; x arrow.length; x) { var a worksheetObjects[sheet2Range2ItemValue].Range(Aarrow[x]).Value2; var b worksheetObjects[sheet2Range2ItemValue].Range(Aarrow[x]).Address(); arrow1.push(a:b); } //遍历删除 for(let i 0;iarrow1.length;i){ array arrow1[i].split(:); if(array[0] ! sheet2Range2ItemValue array[0]! 地市){ worksheetObjects[sheet2Range2ItemValue].Range(array[1]).EntireRow.Clear(); } } var st5LastRow1 worksheetObjects[sheet2Range2ItemValue].Cells(worksheetObjects[sheet2Range2ItemValue].Rows.Count, 1).End(xlUp).Row; for(let y st5LastRow1; y0; y--){ if(Application.WorksheetFunction.CountA(worksheetObjects[sheet2Range2ItemValue].Rows(y))0){ worksheetObjects[sheet2Range2ItemValue].Rows(y).Delete(); } } } //删除源表格Sheet2页,设置不弹出是否删除的提示框执行删除完毕后开启 Application.DisplayAlerts false; ws2.Delete(); Application.DisplayAlerts true; }
