If IsEmpty not returning desired results (1 Viewer)

tmyers

Well-known member
Local time
Today, 11:33
Joined
Sep 8, 2020
Messages
1,090
I am doing a simple for loop to loop through a range and check if a cell is empty and if it is, copy data in a cell on the row, paste it in another column one on the same row then delete the content in the cell it was copied from.

The loop runs, but never seems to trigger true on the if statement.
Code:
    For X = LastRow To 1 Step -1
        If IsEmpty("G" & X) Then
            Cells(X, 2).Text.Copy
            Cells(X, 4).Paste
            Cells(X, 2).Delete
        End If
    Next X
I have changed to if that cell = null and cell = "" and it still wont trigger. I have gone through and made sure the cells in question were empty as well and no change. What am I missing?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:33
Joined
May 7, 2009
Messages
19,169
you missed Range():

If IsEmpty(Range("G" & X).Value)
 

Minty

AWF VIP
Local time
Today, 15:33
Joined
Jul 26, 2013
Messages
10,354
Excel doesn't really understand Null as potential cell contents, so that won't work.
Try
IsBlank()

1624541664021.png
 

tmyers

Well-known member
Local time
Today, 11:33
Joined
Sep 8, 2020
Messages
1,090
Ah I see. Now it is entering the if statement, but is throwing an object required answer. Hm.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:33
Joined
May 7, 2009
Messages
19,169
there is no IsBlank() in Excel VBA?
 

tmyers

Well-known member
Local time
Today, 11:33
Joined
Sep 8, 2020
Messages
1,090
Changed the method I was using to copy and it is working now.
Switched it to:
Code:
    For X = LastRow To 1 Step -1
        If IsEmpty(Range("G" & X).Value) Then
            Range("B" & X).Copy Range("D" & X)
            Cells(X, 2).Delete
        End If
    Next X
 

Users who are viewing this thread

Top Bottom