EXCEL宏常用的函数等

最新推荐文章于 2025-06-11 10:05:23 发布

原创最新推荐文章于 2025-06-11 10:05:23 发布 · 2.6k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#excel #宏

EXCEL 专栏收录该内容

0 篇文章

订阅专栏

关闭屏幕刷新

Application.ScreenUpdating = False

根据单元格内容查询所在行/列

columnIndex = ActiveSheet.UsedRange.Find("内容").Column

不弹出提示窗删除多余工作表

Dim Wb As Workbook
Set Wb = Workbooks.Add '新增工作簿
Wb.Activate
num = Sheets.Count  '删除多余工作表
For i = 2 To num
    Application.DisplayAlerts = False  '关闭EXCEL的提示框
    Worksheets(1).Delete
    Application.DisplayAlerts = True  '取消关闭EXCEL的提示框
Next

打印时的边距设置和单位转换

'行高hg = (240 * 2.7682) / (20 + 3)；列宽比6:34:10:10:10,共70
'A4纸：宽210mm，高297mm，上下左右各20mm，净宽170mm;高257mm
'行高: 1毫米＝2.7682个单位 1厘米＝27.682个单位 1个单位＝0.3612毫米
'列宽： 1毫米＝0.4374个单位 1厘米＝4.374 个单位 1个单位＝2.2862毫米
   With ActiveSheet.PageSetup
        .LeftMargin = Application.CentimetersToPoints(2) '左边距厘米
        .RightMargin = Application.CentimetersToPoints(2) '右边距
        .TopMargin = Application.CentimetersToPoints(2) '上边距
        .BottomMargin = Application.CentimetersToPoints(2) '下边距
        .HeaderMargin = Application.CentimetersToPoints(1) '页眉
        .FooterMargin = Application.CentimetersToPoints(1) '页脚

            .CenterHorizontally = True 'False '水平和垂直居中
         '.CenterVertically = False'水平和垂直居中
    End With

缩小字体以适应单元格大小

Columns(2).Select
With Selection.WrapText = True
        .ShrinkToFit = False
End With

单元格框线

Range(Cells(xinbiao + 2, 1), Cells(xinbiao + 22, 5)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone'上斜线无
Selection.Borders(xlDiagonalUp).LineStyle = xlNone '下斜线无
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous '外边线
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous '外边线
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous '外边线
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous '外边线
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous '内边线
Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous '内边线