0

Part of a larger code, but I'm trying to fill out the blank spaces in a column that I can then delete later.

But if the column doesn't have blank spaces I need it to stop and move onto the next step instead.

I did this by having the code check column A, if Column A is blank than that means there are no blank cells in the column i'm checking.

However, whenever I ask this to do an If/Then statement to check if a particular nearby cell is blank it gives me a 424 error.

No idea what's causing this.

Range("D1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

If ActiveCell.Offset(0, -3) Is Blank Then
GoTo Continue1
Else
End If

ActiveCell.FormulaR1C1 = "DELETEGREENELEPHANTS"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

Continue1:
MRDoubleyou
  • 45
  • 1
  • 8

2 Answers2

1

Is Blank doesn't work. Blank is an undeclared object.

Use IsEmpty:

If IsEmpty(ActiveCell.Offset(0, -3)) Then

I highly suggest you see this question for how to avoid using Select and ActiveCell.

Also the use of GoTo here is generally frowned upon; that's creating spaghetti code. Here's a simplified approach that doesn't use GoTo:

If Not Is Empty(ActiveCell.Offset(0, -3)) Then
   ActiveCell.FormulaR1C1 = "DELETEGREENELEPHANTS"
   ActiveCell.Offset(1, 0).Range("A1").Select
   ActiveCell.Offset(0, -3).Range("A1").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(0, 3).Range("A1").Select
   Range(Selection, Selection.End(xlUp)).Select
   Selection.FillDown
End If

noting that everything inside the If...End If should be rewritten to avoid Select and ActiveCell.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • I suppose I could nest the part that adds "DELETEGREENELEPHANTS" into the Else part of the If/Then statement, but i'd still need to put in the GoTo for it to do something when it's blank, wouldn't I? – MRDoubleyou Jul 28 '20 at 15:57
1

The code for checking is:

IsEmpty(CellValue) - it returns a Boolean.

https://wellsr.com/vba/2016/excel/use-isempty-vba-to-check-if-cell-is-blank/

Philip Joubert
  • 31
  • 3