找到第一个空白行，然后写入它 [英] Finding first blank row, then writing to it

问题描述

我需要找到工作簿中的第一个空白行，并将信息写入（行，1）和（行，2）。我想我现在很卡住了...

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...

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

非常感谢任何帮助。

推荐答案

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

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

使用示例： firstblankRow（thisworkbook.Sheets（1））或传递任何工作表。

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

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

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

这篇关于找到第一个空白行，然后写入它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！