我想知道如何將這個范圍改變 "G" 寫入字串變數strColumn。
這是我要更改的代碼：
Dim lastRowElemento As Integer
lastRowElemento = Cells(Rows.Count, "G").End(xlUp).Row
Set rngElemento = ws.Range("G2:G" & lastRowElemento)
uj5u.com熱心網友回復：
參考“非空”列范圍
其實有兩個要求：
ColumnString = G（我更喜歡字串，因為例如XFD是字母s）FirstRow = 2
如果你把它們放在一起，你會得到
G2（想想一個，而不是兩個變數）。由于在查找列中最底部（最后一個）非空單元格時使用該
Find方法比使用該End屬性更可靠，因此我使用它來撰寫RefColumn在您的情況下可以通過以下方式使用的函式：Set rngElemento = RefColumn(ws.Range("G2"))如果你要測驗是否有資料（通常你知道有），我會留給你，但我更喜歡在代碼中至少保留一個“簡化”的測驗：
If rngElemento is Nothing Then Exit Sub ' no data ' Continue...
編碼
Option Explicit
Sub RefColumnTEST()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
Dim rg As Range: Set rg = RefColumn(ws.Range("G2"))
If rg Is Nothing Then ' the range 'G2:G1048576' is empty
MsgBox "No data.", vbCritical
Else
MsgBox rg.Address(0, 0), vbInformation
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose: Creates a reference to the one-column range from the first cell
' of a range ('FirstCell') to the bottom-most non-empty cell
' of the first cell's worksheet column.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefColumn( _
ByVal FirstCell As Range) _
As Range
If FirstCell Is Nothing Then Exit Function
With FirstCell.Cells(1)
Dim lCell As Range
Set lCell = .Resize(.Worksheet.Rows.Count - .Row 1) _
.Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Exit Function
Set RefColumn = .Resize(lCell.Row - .Row 1)
End With
End Function
滿足您的要求
- 與我的首選方式類似，您可以使用該
RefData功能：
Function RefData( _
ByVal ws As Worksheet, _
ByVal ColumnIndex As Variant) _
As Range
On Error GoTo ClearError
With ws.Columns(ColumnIndex).Resize(ws.Rows.Count - 1).Offset(1)
Set RefData = _
.Resize(.Find("*", , xlFormulas, , , xlPrevious).Row - 1)
End With
ProcExit:
Exit Function
ClearError:
Resume ProcExit
End Function
您可以通過以下方式使用它：
Set rngElemento = RefData(ws, "G")
Set rngElemento = RefData(ws, 7)
' or:
Const strColumn As String = "G"
Set rngElemento = RefData(ws, strColumn)
uj5u.com熱心網友回復：
應用OP的方法，試試這個：
Sub TEST()
Dim ws As Worksheet, Rng As Range, sCol As String
sCol = "G"
Set ws = ThisWorkbook.Sheets("TEST") 'change as required
With ws.Columns(sCol)
Set Rng = Range(.Cells(2), .Cells(.Rows.Count).End(xlUp))
End With
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/339498.html