如何在空白区域中查找第一个和最后一个填充的单元格 [英] 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屋！