vba - 在excel VBA中对几个不相邻的列执行格式化？

Question

使用我的代码，我希望 2 在几个不相邻的列上执行格式化，并且我想在代码中优雅地进行格式化。就像书上说的那样。我正在学习，所以我想以正确的方式学习。
这就是我的任务的样子:



我已阅读 this.
我的意思是......真的没有办法在 COLUMNS 方法中枚举列字母吗？没有办法做到这一点？

那么我需要使用 RANGE 对象来执行我的任务吗？这是对的吗？

如何将 UNION 方法与“With...End With”结合使用
我猜这是我的情况。
请比仅 1,5 行答案更详细地解释。
我需要对列做同样的事情:B、C、G、H。
它们将被格式化为 .NumberFormat = "#,##0.00"

Option Explicit
Dim VBA As Worksheet
Dim Filter As String
Dim Stock As Variant
Dim Index As Variant
Dim Portfolio As Variant
'   Dim Date as Range

Sub Columns_Formatting()

Set VBA = Workbooks("kgh pricing model thursday.xlsm").Worksheets("VBA")
Filter = "Pliki CSV, *.csv," & "Pliki TXT, *.txt," & "All Files, *.*"
'   Stock = Application.GetOpenFilename(fileFilter:=Filter, FilterIndex:=1, Title:="Choose file with a stock prices")
'   Index = Application.GetOpenFilename(fileFilter:=Filter, FilterIndex:=1, Title:="Choose file with an index values")
'   If Stock = False Or Index = False Then MsgBox "Canceled": Exit Sub

With VBA.Columns("A:A,F:F")     '  here is the error = type mismath
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .NumberFormat = "yyyy-mm-dd;@"
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .ColumnWidth = 12
End With

With VBA.Columns("A;F")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .NumberFormat = "General"
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .ColumnWidth = 12
End With

With VBA.Range("E2:E" & Rows.Count)
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .NumberFormat = "#,##0"
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .ColumnWidth = 10
End With

Answer 1

使用 Range object带有完整的列引用。您可能还想减少对 Worksheet.UsedRange property 的完整列引用。与 Intersect method .

With VBA
    With Intersect(.UsedRange, .Range("A:A,F:F"))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .NumberFormat = "yyyy-mm-dd;@"
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .ColumnWidth = 12
    End With
End With