VBA 宏编辑目录一、VBA 宏编辑1. 一键转换净值与清除#N/A格式2. 一键表格为三线表(带内部虚线版)2.1. VB宏编辑器2.2. WPS宏编辑器一、VBA 宏编辑1. 一键转换净值与清除#N/A格式下面集合了通过对B,C,D,E四列的格式转换如把格式为空及公式匹配为#N/A的转换为空将公式转换的值转为净值的操作同时包含有对转换后标题样式及内容区域样式的设置如标题是微软雅黑加粗11号字体且垂直居中区域内容为微软雅黑不加粗10号字体且垂直居中。Sub 一键处理JKLM()Dim ws As Worksheet Set wsActiveSheet Dim lastRowB As Long, lastRowC As Long Dim lastRowD As Long, lastRowE As Long lastRowBws.Cells(ws.Rows.Count,B).End(xlUp).Row lastRowCws.Cells(ws.Rows.Count,C).End(xlUp).Row lastRowDws.Cells(ws.Rows.Count,D).End(xlUp).Row lastRowEws.Cells(ws.Rows.Count,E).End(xlUp).Row If lastRowB2Then lastRowB2If lastRowC2Then lastRowC2If lastRowD2Then lastRowD2If lastRowE2Then lastRowE2 列标题 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).ColumnWidth12.8 J 列依赖 B With ws.Range(J2:J lastRowB) .Formula IF(ISNA(B2),,IF(B2,,B2)) .Value .Value End With K 列依赖 CWith ws.Range(K2:KlastRowC).FormulaIF(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 列依赖 EWith ws.Range(M2:MlastRowE).FormulaIF(ISNA(E2),,IF(E2,,E2)).Value.Value End With 内容区域样式With ws.Range(J2:MApplication.Max(lastRowB, lastRowC, lastRowD, lastRowE)).Font.NameMicrosoft YaHei.Font.Size10.Font.BoldFalse .HorizontalAlignmentxlCenter .VerticalAlignmentxlCenter End With End Sub2. 一键表格为三线表(带内部虚线版)2.1. VB宏编辑器Sub 企业级三线表()Dim tbl As Table For Each tbl In ActiveDocument.Tables On Error Resume Next 自动适应页面宽度 tbl.AutoFitBehavior wdAutoFitWindow 尝试平均分配列宽 tbl.AllowAutoFitFalse tbl.Columns.DistributeWidth 水平居中 tbl.Range.ParagraphFormat.Alignment wdAlignParagraphCenter 垂直居中 tbl.Range.Cells.VerticalAlignmentwdCellAlignVerticalCenter 行高自动 tbl.Rows.HeightRule wdRowHeightAuto 清除所有边框 tbl.Borders.EnableFalse 顶线1.5磅 With tbl.Borders(wdBorderTop) .LineStyle wdLineStyleSingle .LineWidth wdLineWidth150pt End With 底线1.5磅 With tbl.Borders(wdBorderBottom).LineStylewdLineStyleSingle .LineWidthwdLineWidth150pt End With 内部横线点状虚线 With tbl.Borders(wdBorderHorizontal) .LineStyle wdLineStyleDot .LineWidth wdLineWidth050pt End With 内部竖线点状虚线 With tbl.Borders(wdBorderVertical).LineStylewdLineStyleDot .LineWidthwdLineWidth050pt End With 去掉左右边框 tbl.Borders(wdBorderLeft).LineStyle wdLineStyleNone tbl.Borders(wdBorderRight).LineStyle wdLineStyleNone 栏目线第一行下边框 With tbl.Rows(1).Borders(wdBorderBottom).LineStylewdLineStyleSingle .LineWidthwdLineWidth075pt End With On Error GoTo0Next tbl MsgBox企业报告表格样式处理完成, vbInformation End Sub2.2. WPS宏编辑器function标准三线表(){for(let 表格 of ActiveDocument.Tables){// 自动适应页面 表格.AutoFitBehavior(2);// 平均分布列宽 表格.Columns.DistributeWidth();// 水平居中 表格.Range.ParagraphFormat.Alignment1;// 垂直居中 表格.Range.Cells.VerticalAlignment1;// 行高自动 表格.Rows.Height0;// 字体 表格.Range.Font.Name宋体;表格.Range.Font.Size10.5;// 表头加粗if(表格.Rows.Count1){表格.Rows(1).Range.Boldtrue;}// 清除所有边框 表格.Borders.Enablefalse;//顶线1.5磅表格.Borders.Item(-1).LineStyle1;表格.Borders.Item(-1).LineWidth12;//底线1.5磅表格.Borders.Item(-3).LineStyle1;表格.Borders.Item(-3).LineWidth12;//栏目线0.75磅if(表格.Rows.Count1){表格.Rows(1).Borders.Item(-3).LineStyle1;表格.Rows(1).Borders.Item(-3).LineWidth6;}// 去除左右边框 表格.Borders.Item(-2).LineStyle0;表格.Borders.Item(-4).LineStyle0;}} 
长河君 · 2026/11/1 1:34:08 · 阅读 1.2k Excel 之 VBA 宏编辑
VBA 宏编辑目录一、VBA 宏编辑1. 一键转换净值与清除#N/A格式2. 一键表格为三线表(带内部虚线版)2.1. VB宏编辑器2.2. WPS宏编辑器一、VBA 宏编辑1. 一键转换净值与清除#N/A格式下面集合了通过对B,C,D,E四列的格式转换如把格式为空及公式匹配为#N/A的转换为空将公式转换的值转为净值的操作同时包含有对转换后标题样式及内容区域样式的设置如标题是微软雅黑加粗11号字体且垂直居中区域内容为微软雅黑不加粗10号字体且垂直居中。Sub 一键处理JKLM()Dim ws As Worksheet Set wsActiveSheet Dim lastRowB As Long, lastRowC As Long Dim lastRowD As Long, lastRowE As Long lastRowBws.Cells(ws.Rows.Count,B).End(xlUp).Row lastRowCws.Cells(ws.Rows.Count,C).End(xlUp).Row lastRowDws.Cells(ws.Rows.Count,D).End(xlUp).Row lastRowEws.Cells(ws.Rows.Count,E).End(xlUp).Row If lastRowB2Then lastRowB2If lastRowC2Then lastRowC2If lastRowD2Then lastRowD2If lastRowE2Then lastRowE2 列标题 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).ColumnWidth12.8 J 列依赖 B With ws.Range(J2:J lastRowB) .Formula IF(ISNA(B2),,IF(B2,,B2)) .Value .Value End With K 列依赖 CWith ws.Range(K2:KlastRowC).FormulaIF(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 列依赖 EWith ws.Range(M2:MlastRowE).FormulaIF(ISNA(E2),,IF(E2,,E2)).Value.Value End With 内容区域样式With ws.Range(J2:MApplication.Max(lastRowB, lastRowC, lastRowD, lastRowE)).Font.NameMicrosoft YaHei.Font.Size10.Font.BoldFalse .HorizontalAlignmentxlCenter .VerticalAlignmentxlCenter End With End Sub2. 一键表格为三线表(带内部虚线版)2.1. VB宏编辑器Sub 企业级三线表()Dim tbl As Table For Each tbl In ActiveDocument.Tables On Error Resume Next 自动适应页面宽度 tbl.AutoFitBehavior wdAutoFitWindow 尝试平均分配列宽 tbl.AllowAutoFitFalse tbl.Columns.DistributeWidth 水平居中 tbl.Range.ParagraphFormat.Alignment wdAlignParagraphCenter 垂直居中 tbl.Range.Cells.VerticalAlignmentwdCellAlignVerticalCenter 行高自动 tbl.Rows.HeightRule wdRowHeightAuto 清除所有边框 tbl.Borders.EnableFalse 顶线1.5磅 With tbl.Borders(wdBorderTop) .LineStyle wdLineStyleSingle .LineWidth wdLineWidth150pt End With 底线1.5磅 With tbl.Borders(wdBorderBottom).LineStylewdLineStyleSingle .LineWidthwdLineWidth150pt End With 内部横线点状虚线 With tbl.Borders(wdBorderHorizontal) .LineStyle wdLineStyleDot .LineWidth wdLineWidth050pt End With 内部竖线点状虚线 With tbl.Borders(wdBorderVertical).LineStylewdLineStyleDot .LineWidthwdLineWidth050pt End With 去掉左右边框 tbl.Borders(wdBorderLeft).LineStyle wdLineStyleNone tbl.Borders(wdBorderRight).LineStyle wdLineStyleNone 栏目线第一行下边框 With tbl.Rows(1).Borders(wdBorderBottom).LineStylewdLineStyleSingle .LineWidthwdLineWidth075pt End With On Error GoTo0Next tbl MsgBox企业报告表格样式处理完成, vbInformation End Sub2.2. WPS宏编辑器function标准三线表(){for(let 表格 of ActiveDocument.Tables){// 自动适应页面 表格.AutoFitBehavior(2);// 平均分布列宽 表格.Columns.DistributeWidth();// 水平居中 表格.Range.ParagraphFormat.Alignment1;// 垂直居中 表格.Range.Cells.VerticalAlignment1;// 行高自动 表格.Rows.Height0;// 字体 表格.Range.Font.Name宋体;表格.Range.Font.Size10.5;// 表头加粗if(表格.Rows.Count1){表格.Rows(1).Range.Boldtrue;}// 清除所有边框 表格.Borders.Enablefalse;//顶线1.5磅表格.Borders.Item(-1).LineStyle1;表格.Borders.Item(-1).LineWidth12;//底线1.5磅表格.Borders.Item(-3).LineStyle1;表格.Borders.Item(-3).LineWidth12;//栏目线0.75磅if(表格.Rows.Count1){表格.Rows(1).Borders.Item(-3).LineStyle1;表格.Rows(1).Borders.Item(-3).LineWidth6;}// 去除左右边框 表格.Borders.Item(-2).LineStyle0;表格.Borders.Item(-4).LineStyle0;}}