如何在空白区域中查找第一个和最后一个填充的单元格 [英] How to find the first and last populated cell in a range with whitespace
本文介绍了如何在空白区域中查找第一个和最后一个填充的单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
对于宏,我正在使用VBA通过查找填充的第一行和最后一行来确定日期范围.我已经找到一种通过使用End.xlToRight查找填充的第一行和最后一个行的方法,但是只有在它是一个连续填充的行(如:
For a macro I am using VBA to determine a daterange by finding the first and last populated row. I have found a way to find the first and last populated row, by using End.xlToRight, but that only works if it is one continuous filled row like:
183 | 183 | 183 | 183 | 183 | 183 | 183 | 183 |
现在一些行如下:
183 | 183 | 183 | 183 | empty | 183 | 183 | 183 | 183 |
或
183 | 183 | 183 | 183 | 183 | 183 | 183 | 305| 305| 305| 305
什么是找到系列中第一个和最后一个填充单元格(中间有一个空单元格)的坐标的好方法?
What would be a good way to find the coordinates of the first and last populated cells of the series with the empty cell inbetween?
谢谢!
推荐答案
查找方法
Sub FindMethod()
With ThisWorkbook.ActiveSheet
' First Used Row
Dim FirstUR As Long
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then _
FirstUR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count)).Row
' First Used Column
Dim FirstUC As Integer
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then FirstUC = _
.Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), , , 2).Column
' Last Used Row
Dim LastUR As Long
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then LastUR = .Cells.Find("*", , , , , 2).Row
' Last Used Column
Dim LastUC As Integer
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then LastUC = .Cells.Find("*", , , , 2, 2).Column
' First Used Cell (First Cell of the Used Range)
Dim FirstUCell As Range
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then Set FirstUCell = .Cells(.Cells.Find("*", _
.Cells(.Rows.Count, .Columns.Count)).Row, .Cells.Find("*", _
.Cells(.Rows.Count, .Columns.Count), , , 2).Column)
' Last Used Cell (Last Cell of the Used Range)
Dim LastUCell As Range
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then Set LastUCell = .Cells(.Cells.Find("*", , , , 1, 2) _
.Row, .Cells.Find("*", , , , 2, 2).Column)
' Used Range (Not UsedRange)
Dim URng As Range
If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
Is Nothing Then Set URng = .Range(.Cells(.Cells.Find("*", _
.Cells(.Rows.Count, .Columns.Count)).Row, .Cells.Find("*", _
.Cells(.Rows.Count, .Columns.Count), , , 2).Column), .Cells(.Cells _
.Find("*", , , , 1, 2).Row, .Cells.Find("*", , , , 2, 2).Column))
' Usage - Rows, Columns
Debug.Print "First Used Row = " & FirstUR
Debug.Print "First Used Column = " & FirstUC
Debug.Print "Last Used Row = " & LastUR
Debug.Print "Last Used Column = " & LastUC
' Usage - Ranges
If Not FirstUCell Is Nothing Then
Debug.Print "First Used Cell Address = " & FirstUCell.Address
Else
Debug.Print "First Used Cell = Nothing (Empty Sheet)"
End If
If Not LastUCell Is Nothing Then
Debug.Print "Last Used Cell Address = " & LastUCell.Address
Else
Debug.Print "Last Used Cell = Nothing (Empty Sheet)"
End If
If Not FirstUCell Is Nothing Then
Debug.Print "Used Range Address = " & URng.Address
Else
Debug.Print "Used Range = Nothing (Empty Sheet)"
End If
' ' Some Thoughts:
' FirstUR = FirstUCell.Row
' FirstUC = FirstUCell.Column
' LastUR = LastUCell.Row
' LastUC = LastUCell.Column
'
' FirstUR = URng.Row
' FirstUC = URng.Column
' LastUR = URng.Rows.Count - URng.Row + 1
' LastUC = URng.Columns.Count - URng.Column + 1
'
' Set FirstUCell = .Cells(FirstUR, FirstUC)
' Set LastUCell = .Cells(LastUR, LastUC)
'
' Set FirstUCell = .Cells(URng.Row, URng.Column)
' Set FirstUCell = URng.Cells(1, 1)
' Set LastUCell = .Cells(URng.Rows.Count - URng.Row + 1, _
' URng.Columns.Count - URng.Column + 1)
' Set LastUCell = URng.Cells(URng.Rows.Count, URng.Columns.Count)
'
' Set URng = .Range(FirstUCell, LastUCell)
' Set URng = .Range(.Cells(FirstUR, FirstUC), .Cells(LastUR, LastUC))
' Set URng = .Range(FirstUCell.Address & ":" & LastUCell.Address)
' Various Resize and Offset possibilities
End With
End Sub
这篇关于如何在空白区域中查找第一个和最后一个填充的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文