vba 找到第一个空白行，然后写入

Finding first blank row, then writing to it

提问by okapishomapi

I need to find the first blank row in a workbook and write information to (row, 1) and (row, 2). I think I'm currently pretty stuck...

我需要在工作簿中找到第一个空白行并将信息写入 (row, 1) 和 (row, 2)。我想我目前很困...

Function WriteToMaster(num, path) As Boolean

'Declare variables
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim infoLoc As Integer

Set xlApp = New Excel.Application

Set wb = xlApp.Workbooks.Open("PATH OF THE DOC")
Set ws = wb.Worksheets("Sheet1")

'Loop through cells, looking for an empty one, and set that to the Num
Cells(1, 1).Select
For Each Cell In ws.UsedRange.Cells
    If Cell.Value = "" Then Cell = Num
    MsgBox "Checking cell " & Cell & " for value."
Next


'Save, close, and quit
wb.Save
wb.Close
xlApp.Quit

'Resets the variables
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

Thanks so much for any help.

非常感谢您的帮助。

回答by Daniel

If you mean the row number after the last row that is used, you can find it with this:

如果您的意思是使用的最后一行之后的行号，您可以通过以下方式找到它：

Dim unusedRow As Long
unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row

If you mean a row that happens to be blank with data after it... it gets more complicated.

如果你的意思是一行后面的数据恰好是空白的......它会变得更加复杂。

Here's a function I wrote which will give you the actual row number of the first row that is blank for the provided worksheet.

这是我编写的一个函数，它将为您提供所提供工作表中空白的第一行的实际行号。

Function firstBlankRow(ws As Worksheet) As Long
'returns the row # of the row after the last used row
'Or the first row with no data in it
    Dim rw As Range
    For Each rw In ws.UsedRange.Rows
        If rw.Address = ws.Range(rw.Address).SpecialCells(xlCellTypeBlanks). _
            Address Then

                firstBlankRow = rw.Row
                Exit For
        End If
    Next
    If firstBlankRow = 0 Then
        firstBlankRow = ws.Cells.SpecialCells(xlCellTypeLastCell). _
                    Offset(1, 0).Row
    End If
End Function

Usage example: firstblankRow(thisworkbook.Sheets(1))or pass any worksheet.

用法示例：firstblankRow(thisworkbook.Sheets(1))或传递任何工作表。

Edit: As ooo pointed out, this will error if there are no blank cells in your used range.

编辑：正如 ooo 所指出的，如果您使用的范围内没有空白单元格，则会出错。

回答by user3357963

I would have done it like this. Short and sweet :)

我会这样做的。简短而甜蜜:)

Sub test()
Dim rngToSearch As Range
Dim FirstBlankCell As Range
Dim firstEmptyRow As Long

Set rngToSearch = Sheet1.Range("A:A")
    'Check first cell isn't empty
    If IsEmpty(rngToSearch.Cells(1, 1)) Then
        firstEmptyRow = rngToSearch.Cells(1, 1).Row
    Else
        Set FirstBlankCell = rngToSearch.FindNext(After:=rngToSearch.Cells(1, 1))
        If Not FirstBlankCell Is Nothing Then
            firstEmptyRow = FirstBlankCell.Row
        Else
            'no empty cell in range searched
        End If
    End If
End Sub

Updated to check if first row is empty.

更新以检查第一行是否为空。

Edit: Update to include check if entire row is empty

编辑：更新以包括检查整行是否为空

Option Explicit

Sub test()
Dim rngToSearch As Range
Dim firstblankrownumber As Long

    Set rngToSearch = Sheet1.Range("A1:C200")
    firstblankrownumber = FirstBlankRow(rngToSearch)
    Debug.Print firstblankrownumber

End Sub

Function FirstBlankRow(ByVal rngToSearch As Range, Optional activeCell As Range) As Long
Dim FirstBlankCell As Range

    If activeCell Is Nothing Then Set activeCell = rngToSearch.Cells(1, 1)
    'Check first cell isn't empty
    If WorksheetFunction.CountA(rngToSearch.Cells(1, 1).EntireRow) = 0 Then
        FirstBlankRow = rngToSearch.Cells(1, 1).Row
    Else

        Set FirstBlankCell = rngToSearch.FindNext(After:=activeCell)
        If Not FirstBlankCell Is Nothing Then

            If WorksheetFunction.CountA(FirstBlankCell.EntireRow) = 0 Then
                FirstBlankRow = FirstBlankCell.Row
            Else
                Set activeCell = FirstBlankCell
                FirstBlankRow = FirstBlankRow(rngToSearch, activeCell)

            End If
        Else
            'no empty cell in range searched
        End If
    End If
End Function

回答by Scott Holtzman

Update

更新

Inspired by Daniel's code above and the fact that this is WAY! more interesting to me now then the actual work I have to do, i created a hopefully full-proof function to find the first blank row in a sheet. Improvements welcome! Otherwise, this is going to my library :) Hopefully others benefit as well.

受上面 Daniel 的代码的启发，以及这是 WAY 的事实！现在对我来说更有趣的是我必须做的实际工作，我创建了一个希望完整的功能来查找工作表中的第一个空白行。欢迎改进！否则，这将进入我的图书馆 :) 希望其他人也能受益。

    Function firstBlankRow(ws As Worksheet) As Long
'returns the row # of the row after the last used row
'Or the first row with no data in it

    Dim rngSearch As Range, cel As Range

    With ws

        Set rngSearch = .UsedRange.Columns(1).Find("") '-> does blank exist in the first column of usedRange

        If Not rngSearch Is Nothing Then

            Set rngSearch = .UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks)

            For Each cel In rngSearch

                If Application.WorksheetFunction.CountA(cel.EntireRow) = 0 Then

                    firstBlankRow = cel.Row
                    Exit For

                End If

            Next

        Else '-> no blanks in first column of used range

            If Application.WorksheetFunction.CountA(Cells(.Rows.Count, 1).EntireRow) = 0 Then '-> is the last row of the sheet blank?

                '-> yeap!, then no blank rows!
                MsgBox "Whoa! All rows in sheet are used. No blank rows exist!"


            Else

                '-> okay, blank row exists
                firstBlankRow = .UsedRange.SpecialCells(xlCellTypeBlanks).Row + 1

            End If

        End If

    End With

End Function

Original Answer

原答案

To find the first blank in a sheet, replace this part of your code:

要查找工作表中的第一个空白，请替换代码的这一部分：

Cells(1, 1).Select
For Each Cell In ws.UsedRange.Cells
    If Cell.Value = "" Then Cell = Num
    MsgBox "Checking cell " & Cell & " for value."
Next

With this code:

使用此代码：

With ws

    Dim rngBlanks As Range, cel As Range

    Set rngBlanks = Intersect(.UsedRange, .Columns(1)).Find("")

    If Not rngBlanks Is Nothing Then '-> make sure blank cell exists in first column of usedrange
        '-> find all blank rows in column A within the used range
        Set rngBlanks = Intersect(.UsedRange, .Columns(1)).SpecialCells(xlCellTypeBlanks)

        For Each cel In rngBlanks '-> loop through blanks in column A

            '-> do a countA on the entire row, if it's 0, there is nothing in the row
            If Application.WorksheetFunction.CountA(cel.EntireRow) = 0 Then
                num = cel.Row
                Exit For
            End If

        Next
    Else

        num = usedRange.SpecialCells(xlCellTypeLastCell).Offset(1).Row                 

    End If


End With

回答by user3393711

I know this is an older thread however I needed to write a function that returned the first blank row WITHIN a range. All of the code I found online actually searches the entire row (even the cells outside of the range) for a blank row. Data in ranges outside the search range was triggering a used row. This seemed to me to be a simple solution:

我知道这是一个较旧的线程，但是我需要编写一个函数来返回范围内的第一个空白行。我在网上找到的所有代码实际上都是在整行（甚至是范围外的单元格）中搜索空白行。搜索范围之外的范围内的数据正在触发已使用的行。在我看来，这是一个简单的解决方案：

Function FirstBlankRow(ByVal rngToSearch As Range) As Long
   Dim R As Range
   Dim C As Range
   Dim RowIsBlank As Boolean

   For Each R In rngToSearch.Rows
      RowIsBlank = True
      For Each C In R.Cells
         If IsEmpty(C.Value) = False Then RowIsBlank = False
      Next C
      If RowIsBlank Then
         FirstBlankRow = R.Row
         Exit For
      End If
   Next R
End Function

回答by ultr4low

very old thread but .. i was lookin for an "easier"... a smaller code

非常旧的线程，但是..我正在寻找一个“更容易”的......一个较小的代码

i honestly dont understand any of the answers above :D - i′m a noob

老实说，我不明白上面的任何答案：D - 我是菜鸟

but this should do the job. (for smaller sheets)

但这应该可以完成工作。（对于较小的纸张）

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

reads every cell in col 1 from bottom up and stops at first empty cell

从下往上读取第 1 列中的每个单元格并在第一个空单元格处停止

intRow = 1
Do until objExcel.Cells(intRow, 1).Value = ""
   intRow = intRow + 1
Loop

then you can write your info like this

然后你可以像这样写你的信息

objExcel.Cells(intRow, 1).Value = "first emtpy row, col 1"
objExcel.Cells(intRow, 2).Value = "first emtpy row, col 2"

etc...

等等...

and then i recognize its an vba thread ... lol

然后我认出它是一个 vba 线程...大声笑

回答by quantum285

Very old thread but a simpler take :)

很旧的线程，但更简单：)

Sub firstBlank(c) 'as letter
    MsgBox (c & Split(Range(c & ":" & c).Find("", LookIn:=xlValues).address, "$")(2))
End Sub
Sub firstBlank(c) 'as number
    cLet = Split(Cells(1, c).address, "$")(1)
    MsgBox (cLet & Split(Range(cLet & ":" & cLet).Find("", LookIn:=xlValues).address, "$")(2))
End Sub

回答by user8453101

Function firstBlankRow() As Long
Dim emptyCells As Boolean

函数 firstBlankRow() As Long
Dim emptyCells As Boolean

    For Each rowinC In Sheet7.Range("A" & currentEmptyRow & ":A5000")   ' (row,col)

        If rowinC.Value = "" Then
            currentEmptyRow = rowinC.row
            'firstBlankRow = rowinC.row 'define class variable to simplify computing complexity for other functions i.e. no need to call function again
            Exit Function   
        End If
    Next

End Function

结束函数

回答by Josh Z

ActiveSheet.Range("A10000").End(xlup).offset(1,0).Select