在我的作業簿中，Column 我包含日期。我可以通過以下方式輕松獲得最后一行：
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row
如果第一次出現的單元格等于今天，我需要將該列的行放在變數 (Long) 中。
實際上，預期的代碼是這樣的：
Set Rng = ActiveSheet.Range("I" & FirstRow & ":I" & LastRow)
注意： 使用VBA AutoFilter不適用于我的作業簿，因為它同時受到保護和共享
示例表鏈接
uj5u.com熱心網友回復：
使用 Find 方法參考范圍
- 此解決方案將在列中查找今天第一次出現的日期，并創建對從該單元格到同一列中最底部的非空單元格的范圍的參考。
- 該
RefTodaysRangeTEST程序說明了如何使用該RefTodaysRange功能（要走的路）。 - 該
TodaysRange程序在不使用函式的情況下執行相同的操作，但會使您的代碼變得混亂。 - 該
TodaysRangeDebugPrintStudy程序將各個階段的范圍地址列印到立即視窗 ( Crtl G)。
Option Explicit
Sub RefTodaysRangeTEST()
Const fCellAddress = "A3"
Dim ws As Worksheet: Set ws = ActiveSheet
Dim fCell As Range: Set fCell = ws.Range(fCellAddress)
Dim trg As Range: Set trg = RefTodaysRange(fCell)
' Continue, e.g.:
If Not fCell Is Nothing Then
MsgBox "Today's Range Address: " & trg.Address(0, 0)
Else
MsgBox "Today's Range Address: not available."
End If
End Sub
Function RefTodaysRange( _
FirstCell As Range) _
As Range
If FirstCell Is Nothing Then Exit Function
Dim lCell As Range ' last (bottom-most) non-empty cell
Dim fCell As Range ' first (top-most) cell containing today's date
With FirstCell
Dim crg As Range
Set crg = .Resize(.Worksheet.Rows.Count - .Row 1)
Set lCell = crg.Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Exit Function ' no data
Set crg = .Resize(lCell.Row - .Row 1)
Set fCell = crg.Find(Date, lCell, xlValues, xlWhole)
If fCell Is Nothing Then Exit Function ' today's date not found
End With
Set RefTodaysRange = fCell.Resize(lCell.Row - fCell.Row 1)
End Function
Sub TodaysRange()
Const fCellAddress = "A3"
Dim ws As Worksheet: Set ws = ActiveSheet
Dim fCell As Range: Set fCell = ws.Range(fCellAddress)
Dim crg As Range: Set crg = fCell.Resize(ws.Rows.Count - fCell.Row 1)
Dim lCell As Range: Set lCell = crg.Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Exit Sub ' no data from 'fCell' to the bottom
Set crg = fCell.Resize(lCell.Row - fCell.Row 1)
Set fCell = crg.Find(Date, lCell, xlValues, xlWhole)
If fCell Is Nothing Then Exit Sub ' today's date not found
Set crg = ws.Range(fCell, lCell)
End Sub
Sub TodaysRangeDebugPrintStudy()
Const fCellAddress = "A3"
Dim ws As Worksheet: Set ws = ActiveSheet
Debug.Print "Worksheet: " & ws.Name
Dim fCell As Range: Set fCell = ws.Range(fCellAddress)
Debug.Print "First Cell: " & fCell.Address(0, 0)
Dim crg As Range: Set crg = fCell.Resize(ws.Rows.Count - fCell.Row 1)
Debug.Print "Column Range: " & crg.Address(0, 0)
Dim lCell As Range: Set lCell = crg.Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Exit Sub ' no data from 'fCell' to the bottom
Debug.Print "Last Cell: " & lCell.Address(0, 0)
Set crg = fCell.Resize(lCell.Row - fCell.Row 1)
Debug.Print "Column Range: " & crg.Address(0, 0)
Set fCell = crg.Find(Date, lCell, xlValues, xlWhole)
If fCell Is Nothing Then Exit Sub ' today's date not found
Debug.Print "First Cell: " & fCell.Address(0, 0)
Set crg = ws.Range(fCell, lCell)
Debug.Print "Column Range: " & crg.Address(0, 0)
End Sub
uj5u.com熱心網友回復：
請測驗下一個簡單的代碼。所有功勞都歸功于@Simon，他清楚地描述了要做什么。我只將它放在適當的位置，使用Variant( mtch) 變數，即使發生錯誤（在沒有任何匹配的情況下）也能被檢查：
Sub testFirstLastCell()
Sub testFirstLastCell()
Dim sh As Worksheet, firstCell As Long, lastCell As Long, rng As Range, mtch
Set sh = ActiveSheet 'use here the sheet you need
lastCell = sh.Range("I" & sh.rows.Count).End(xlUp).row
mtch = Application.match(CLng(Date), sh.Range("I1:I" & lastCell), 0)
If IsNumeric(mtch) Then
firstCell = mtch
Set rng = sh.Range("I" & firstCell, "I" & lastCell)
Else
MsgBox "Today date could not be found..."
End If
If Not rng Is Nothing Then Debug.Print rng.Address
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/402300.html
標籤：
下一篇：VBA獲取陣列第一維的最大值