How find first empty row without .activate or .select?

Question

I need to copy data from one workbook to another over time. So I open the second workbook several times a month and look for the first empty row and add some data with for cycle.

Workbooks(wName).Activate    
  for cycle = 1 to 10
firstEmptyRow = (Workbooks(wName).Worksheets("sName").Cells(Rows.Count, "A").End(xlUp).Row) + 1

Workbooks(wName).Worksheets("sName").Range("A" & (firstEmptyRow)) = myData1 'from userForm
Workbooks(wName).Worksheets("sName").Range("B" & (firstEmptyRow)) = myData2
 next cycle

But is there a beter way? Without .Activate and without .Select?

What is the best practice? I only found this .End(xlUp) code

that is one of the reccomendes ways to get the last row. It is basically equivalent to going to the last possible row of excel and then press `Ctrl`+`Up` in the defined column. — MGP, May 17 '21 at 11:24
I work with multiple workbooks and try to avoid "accidents" caused .Select .Activate and similar. — excel222, May 17 '21 at 11:30
[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Darren Bartrup-Cook, May 17 '21 at 11:39
https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — Siddharth Rout, May 17 '21 at 13:40

score 0 · Answer 1 · answered May 17 '21 at 11:50

In general, when working with several Workbooks, try to save them in objects. For example

Sub copy()
    Dim wbCopyTo As Workbook, wbCopyFrom As Workbook
    Dim wsCopyTo As Worksheet, wsCopyFrom As Worksheet
    Dim lastRow As Long
    Dim cycle As Long
    
    Set wbCopyTo = ThisWorkbook 'ThisWorkbook is the workbook the macro lives in
    Set wbCopyFrom = Workbooks.Open("path_to_workbook")
    
    Set wsCopyTo = wbCopyFrom.Worksheets("sName")
    
    'Do Stuff and get myData1 and myData2
    
    With wsCopyTo
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        For cycle = 1 To 10
            .Cells(lastRow + cycle, 1).Value = myData1
            .Cells(lastRow + cycle, 2).Value = myData2
        Next
    End With
End Sub

I stored the worksheet in an object and will always be sure that the reference is correct.

score 0 · Answer 2 · answered May 17 '21 at 13:26

First Available Row

Adjust the values in the constants section.
The Cycle - myData_ stuff makes little sense here, but the focus is set to the 'handling' of dCell anyway.

Option Explicit

Sub backupData()

    Const dwbPath As String = "C:\Test\Backup.xlsx"
    Const dwsName As String = "sName"
    Const dFirst As String = "A2"
    
    ' Open the workbook if it's not already open.
    Dim dwb As Workbook: Set dwb = Workbooks.Open(dwbPath)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dwsName)
    
    ' Create a reference to the first available cell. This will search
    ' the complete worksheet i.e. if the last value in column `A` is in row `5`,
    ' and the last value in column `C` is in row `7`, then cell `A8`
    ' will be referenced.
    Dim dCell As Range: Set dCell = dws.Range(dFirst)
    With dCell
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1, _
            .Worksheet.Columns.Count - .Column + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If Not lCell Is Nothing Then
            Set dCell = .Offset(lCell.Row - .Row + 1)
            'Debug.Print lCell.Address, dCell.Address
        End If
    End With
    
    ' Remove those two.
    Const myData1 As Long = 0
    Const myData2 As Long = 1
    
    Dim Cycle As Long
    For Cycle = 1 To 10
        dCell.Value = myData1
        dCell.Offset(, 1) = myData2
        Set dCell = dCell.Offset(1)
    Next Cycle

End Sub

How find first empty row without .activate or .select?

2 Answers2

First Available Row