-1

I'm new to VBA.

2 worksheets in the same workbook. I want to get the last empty row number from worksheet 2 and past that number into a cell on worksheet 1.

Anyone, please help. Thanks!

Community
  • 1
  • 1
user3745841
  • 1

1 Answers1

0

enter code hereThe last empty row in a worksheet is usually at or near the bottom the the worksheet. So we start at the end and work our way up:

Sub qwerty()
    Dim N As Long, wf As WorksheetFunction
    Dim s1 As Worksheet, s2 As Worksheet
    Set wf = Application.WorksheetFunction

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")

    For N = Rows.Count To 1 Step -1
    If wf.CountA(s2.Cells(N, 1).EntireRow) = 0 Then
        s1.Range("B9").Value = N
        Exit Sub
    End If
    Next N
End Sub

EDIT#1:

To get the first empty row rather than the last empty row, use:

Sub qwerty()
        Dim N As Long, wf As WorksheetFunction
        Dim s1 As Worksheet, s2 As Worksheet
        Set wf = Application.WorksheetFunction

        Set s1 = Sheets("Sheet1")
        Set s2 = Sheets("Sheet2")

        For N = 1 to Rows.Count
        If wf.CountA(s2.Cells(N, 1).EntireRow) = 0 Then
            s1.Range("B9").Value = N
            Exit Sub
        End If
        Next N
    End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thank you. That gives me the last empty row of that worksheet but not the next available empty row. For example, I want to enter customer information on the next available empty row, sequential order. Sorry if I'm not getting it... – user3745841 Jun 16 '14 at 18:32
  • I think I better clarify further. This is a database of customers. Each record is a customer. So, when I have a new customer, I enter that customers information on the next empty row. – user3745841 Jun 16 '14 at 18:38
  • You requested the **LAST** empty row not the **FIRST.** ...................see my **EDIT#1** – Gary's Student Jun 16 '14 at 18:45