惠州网站建设领头,wordpress 导入用户名密码,深圳网站制作费用多少,it软件开发培训学校源表格首列名称中一样的分别创建该名称的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; }
