vba - 即使有空单元格也可以选择列的 Excel 代码

Question

嗨，我目前正在使用此代码，但它只选择该列，直到找到一个空单元格，我想要的是从 H3 单元格开始选择该列，直到该列中的最后一个值，即使有空行

Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select

Answer 1

让它变得简单。首先，声明一个变量，它可以保存您感兴趣的列中使用的最后一行，并声明一个变量来保存范围并设置它。从长远来看，它将对您有所帮助。

例如看下面的代码...

Sub Test()
Dim LastRow As Long
Dim Rng As Range

'This will find the last row used in column H
LastRow = Cells(Rows.Count, "H").End(xlUp).Row

'Set the Rng variable
Set Rng = Range("H3:H" & LastRow)

'Now do whatever you like to do with this range, like
Rng.Select
MsgBox Rng.Address
Rng.Interior.Color = vbYellow
'etc

'If you want to perform multiple actions on the same range, you can also use With and End With block like below

With Rng
    .Value = "Test"
    .Font.Size = 14
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .RowHeight = 25
    'etc
End With
End Sub