0

I'm trying to call a Sub (New_Row) when the first empty row (minus the last column) is filled. I'm having trouble with how to reference a range of cells in the If statement toward the end. 

Sub Data_Added()
'Check if anything has been entered into the first empty row in "Data"

     Dim sht As Worksheet
     Dim LastRow As Long
     Dim LastColumn As Long
     Dim StartCell As Range

     Sheets("Data").Select
     Set sht = Worksheets("Data")
     Set StartCell = Range("A1").End(xlDown).Select

     Worksheets("Data").UsedRange

     LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
     LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

     Set InputRange = sht.Range(StartCell, sht.Cells(LastRow + 1, LastColumn - 1))

     If InputRange Is Not Nothing Then
          Call New_Row
     End If
End Sub

I've seen people using the Application.Intersect method, but I'm not sure if an intersect makes sense for just one row of cells. Totally new to VBA, though, so I don't know. Right now I'm getting an "Invalid use of Object" error pointing at the "Nothing" in the If statement.

RockHopper
  • 65
  • 1
  • 4
  • 8
  • See [here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for better ways to find the "last" cell in a range/worksheet/etc. – David Zemens Jul 11 '16 at 18:39
  • 1
    in addition to what @DavidZemens wrote, the correct syntax for the `IF` statement is `If Not InputRange is Nothing Then` – Scott Holtzman Jul 11 '16 at 18:46

1 Answers1

2
Dim y As Long, lastx As Long
Dim sht As Worksheet

y = 1  'Row you want to check

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastx = sht.Cells(y, sht.Columns.Count).End(xlToRight).Column - 1

If WorksheetFunction.CountA(Range(Cells(y, 1), Cells(y, lastx))) <> 0 Then      'Call New_Row when the row you are checking is empty
    Call New_Row
End If

Have you tried something like this?

David Zemens
  • 53,033
  • 11
  • 81
  • 130
Benno Grimm
  • 533
  • 1
  • 8
  • 16