CODEWITHSUNDEEP
vbaexcel
RockHopper
RockHopper

Reputation: 65

Checking if there is any data entered into a range of cells using VBA in Excel

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.

Upvotes: 0

Views: 3140

Answers (1)

Benno Grimm
Benno Grimm

Reputation: 540

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?

Upvotes: 2

Related Questions