vba - 如何格式化从特定行到下的 Excel 列

Question

从 B5、C5、D5、...开始，我有一个 excel 文件，如下所示:



请注意，我在前 3 行有一些标题和标题文本，因此我需要使用宏来根据行的标题设置从第 5 行到结尾的每一列的类型(标题仅用于提及所需的类型)可以请让我知道如何在 Excel VBA 中做到这一点？

谢谢

Answer 1

这是你正在尝试的吗？我正在展示 Column B 的示例.做剩下的。

逻辑 :

在列中查找最后一行。见 THIS

构建您的范围

根据需要格式化范围。

代码 :

Private Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, Header As Long

    Header = 5 '<~~ Start row for formatting

    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        With .Range("B" & Header & ":B" & LastRow)
            '
            '~~> Change format here
            '
             '~~> Number with 5 decimal places.
            .NumberFormat = "0.00000"
        End With
    End With
End Sub

评论跟进

Thanks but this just formatting the B5 cell, can you please let me know how i can do the rest of rows from 5 to like 1000 – Behseini 11 secs ago

哦，所以如果第 5 行之后没有值，并且您想硬编码最后一行，请使用此代码

Private Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, Header As Long

    Header = 5     '<~~ Start row for formatting
    LastRow = 1000 '<~~ Last Row

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        With .Range("B" & Header & ":B" & LastRow)
            '
            '~~> Change format here
            '
            '~~> Number with 5 decimal places.
            .NumberFormat = "0.00000"
        End With
    End With
End Sub