VBA 宏编辑

体且垂直居中区域内容为微软雅黑不加粗10号字体且垂直居中。Sub 一键处理JKLM() Dim ws As Worksheet Set ws ActiveSheet Dim lastRowB As Long, lastRowC As Long Dim lastRowD As Long, lastRowE As Long lastRowB ws.Cells(ws.Rows.Count, B).End(xlUp).Row lastRowC ws.Cells(ws.Rows.Count, C).End(xlUp).Row lastRowD ws.Cells(ws.Rows.Count, D).End(xlUp).Row lastRowE ws.Cells(ws.Rows.Count, E).End(xlUp).Row If lastRowB 2 Then lastRowB 2 If lastRowC 2 Then lastRowC 2 If lastRowD 2 Then lastRowD 2 If lastRowE 2 Then lastRowE 2 列标题 With ws.Range(J1:M1) .Value Array(物料编码, 转化SKU, 转化项目号, 转化95码) .Font.Name Microsoft YaHei .Font.Size 11 .Font.Bold True .HorizontalAlignment xlCenter .VerticalAlignment xlCenter End With 设置列宽 ws.Columns(J:M).ColumnWidth 12.8 J 列依赖 B With ws.Range(J2:J lastRowB) .Formula IF(ISNA(B2),,IF(B2,,B2)) .Value .Value End With K 列依赖 C With ws.Range(K2:K lastRowC) .Formula IF(ISNA(C2),,IF(C2,,C2)) .Value .Value End With L 列依赖 D With ws.Range(L2:L lastRowD) .Formula IF(ISNA(D2),,IF(LEN(D2)4,D2J,LEFT(D2,5))) .Value .Value End With M 列依赖 E With ws.Range(M2:M lastRowE) .Formula IF(ISNA(E2),,IF(E2,,E2)) .Value .Value End With 内容区域样式 With ws.Range(J2:M Application.Max(lastRowB, lastRowC, lastRowD, lastRowE)) .Font.Name Microsoft YaHei .Font.Size 10 .Font.Bold False .HorizontalAlignment xlCenter .VerticalAlignment xlCenter End With End Sub一键表格为三线表(带内部虚线版)VB宏编辑器Sub 企业级三线表() Dim tbl As Table For Each tbl In ActiveDocument.Tables On Error Resume Next 自动适应页面宽度 tbl.AutoFitBehavior wdAutoFitWindow 尝试平均分配列宽 tbl.AllowAutoFit False tbl.Columns.DistributeWidth 水平居中 tbl.Range.ParagraphFormat.Alignment wdAlignParagraphCenter 垂直居中 tbl.Range.Cells.VerticalAlignment wdCellAlignVerticalCenter 行高自动 tbl.Rows.HeightRule wdRowHeightAuto 清除所有边框 tbl.Borders.Enable False 顶线1.5磅 With tbl.Borders(wdBorderTop) .LineStyle wdLineStyleSingle .LineWidth wdLineWidth150pt End With 底线1.5磅 With tbl.Borders(wdBorderBottom) .LineStyle wdLineStyleSingle .LineWidth wdLineWidth150pt End With 内部横线点状虚线 With tbl.Borders(wdBorderHorizontal) .LineStyle wdLineStyleDot .LineWidth wdLineWidth050pt End With 内部竖线点状虚线 With tbl.Borders(wdBorderVertical) .LineStyle wdLineStyleDot .LineWidth wdLineWidth050pt End With 去掉左右边框 tbl.Borders(wdBorderLeft).LineStyle wdLineStyleNone tbl.Borders(wdBorderRight).LineStyle wdLineStyleNone 栏目线第一行下边框 With tbl.Rows(1).Borders(wdBorderBottom) .LineStyle wdLineStyleSingle .LineWidth wdLineWidth075pt End With On Error GoTo 0 Next tbl MsgBox 企业报告表格样式处理完成, vbInformation End SubWPS宏编辑器function 标准三线表() { for (let 表格 of ActiveDocument.Tables) { // 自动适应页面 表格.AutoFitBehavior(2); // 平均分布列宽 表格.Columns.DistributeWidth(); // 水平居中 表格.Range.ParagraphFormat.Alignment 1; // 垂直居中 表格.Range.Cells.VerticalAlignment 1; // 行高自动 表格.Rows.Height 0; // 字体 表格.Range.Font.Name 宋体; 表格.Range.Font.Size 10.5; // 表头加粗 if (表格.Rows.Count 1) { 表格.Rows(1).Range.Bold true; } // 清除所有边框 表格.Borders.Enable false; // 顶线1.5磅 表格.Borders.Item(-1).LineStyle 1; 表格.Borders.Item(-1).LineWidth 12; // 底线1.5磅 表格.Borders.Item(-3).LineStyle 1; 表格.Borders.Item(-3).LineWidth 12; // 栏目线0.75磅 if (表格.Rows.Count 1) { 表格.Rows(1).Borders.Item(-3).LineStyle 1; 表格.Rows(1).Borders.Item(-3).LineWidth 6; } // 去除左右边框 表格.Borders.Item(-2).LineStyle 0; 表格.Borders.Item(-4).LineStyle 0; } }