|
|
任务完成：
function 全行数据一览无余(){
while(!rang){
var ran=Application.InputBox("请选个列标题单元格：","参数设置1","","","","","",8); //输入对话框
if(!ran){
return; //退出过程
}
var rang=Intersect(ran,ran.Parent.UsedRange); //是否选择了有效区域
if(!rang){
MsgBox("请选个列标题单元格。",jsQuestion,"金山提醒："); //输出对话框
}
}
var r=ran.Row; //引用单元格的行号、列号、当前区域总列数、工作表名称
var c1=ran.End(xlToLeft).Column;
var c2=ran.CurrentRegion.Columns.Count;
var n=ran.Parent.Name;
Worksheets(n).Cells(r,c1).Resize(1,c2).Copy(); //复制
Sheets.Add(); //新建工作表
Cells(2).PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone, false, true); //转置粘贴
Cells(1).Formula = "1";
Cells(1).AutoFill(Range("A1:A" + c2), xlFillDefault); //填充自然数
while(!rang2){
var ran2=Application.InputBox("请选个数据单元格：","参数设置2","","","","","",8);
if(!ran2){
return;
}
var rang2=Intersect(ran2,ran2.Parent.UsedRange);
if(!rang2){
MsgBox("请选个数据单元格。",jsQuestion,"金山提醒：");
}
}
Worksheets(n).Cells(ran2.Row,c1).Resize(1,c2).Copy();
Cells(3).PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone, false, true);
with(ActiveSheet.UsedRange){
WrapText=false //不自动换行
Interior.Pattern = xlPatternNone; //无填充颜色
}
with(ActiveSheet.UsedRange.Font){
Bold = false; //字体不加粗
ColorIndex = xlColorIndexAutomatic; //字体颜色默认
}
with(ActiveSheet.UsedRange.Borders){
ColorIndex = xlColorIndexAutomatic; //边框颜色自动，6种边框可单独设置.Item()
LineStyle = xlContinuous; //采用细边框
}
if(c2<51){
Columns.Item("A:C").AutoFit(); //不分栏排版3列列宽正好容纳
}
else{
if(c2%2==0){ //哎呀，余数是这么求的！函数放着不用靠边站了
Cells(c2/2+1,1).Resize(c2/2,3).Cut(); //剪切
Cells(4).Select();
ActiveSheet.Paste(); //回车粘贴法
Cells(1).Resize(c2/2,3).Borders.Item(xlEdgeRight).LineStyle = xlDouble; //分栏的边框线采用双线
}
else{
Cells(c2/2+1.5,1).Resize(c2/2+0.5,3).Cut();
Cells(4).Select();
ActiveSheet.Paste();
Cells(1).Resize(c2/2+0.5,3).Borders.Item(xlEdgeRight).LineStyle = xlDouble;
}
Columns.Item("A:F").AutoFit(); //每列都自动适应列宽
}
Range("G1").Select();
MsgBox("排版完毕。")
} |
|
楼主: ctp_119
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2024-5-2 12:51
