Vba, Is there a better way to test empty cells?

Hwyn · #1 11-14-2011, 04:58 AM

Hello everyone, I have some working Excel 2007 code, but i was wondering if there is a shorter way of testing consecutive cells in a row to see if they are empty rather than testing each cell individually, typically columns "J" to "Q" .This is my first post, apologies if i haven't structured my question properly.

Sub

Dim rng As Range, cell As Range
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set rng = Range("a2:a" & LastRow)
For Each cell In rng
If cell.Value Like "*FUJ*" And _
IsEmpty(Cells(cell.Row, "j")) And IsEmpty(Cells(cell.Row, "k")) And _
IsEmpty(Cells(cell.Row, "l")) And IsEmpty(Cells(cell.Row, "m")) And _
IsEmpty(Cells(cell.Row, "n")) And IsEmpty(Cells(cell.Row, "o")) And _
IsEmpty(Cells(cell.Row, "p")) And IsEmpty(Cells(cell.Row, "q")) = True Then
Cells(cell.Row, "u").Value = "Out of Stock"
Else
Cells(cell.Row, "u").Value = "In Stock"
End If
Next
End Sub

Regards Robert

macropod · #2 11-14-2011, 05:46 AM

Hi Robert,

The following should be more efficient:

Code:

Sub Test()
Application.ScreenUpdating = False
Dim LastRow As Long, Rng As Range, oCel As Range, i As Long, bState As Boolean
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set Rng = Range("a2:a" & LastRow)
For Each oCel In Rng
  With oCel
    bState = True
    If .Value Like "*FUJ*" Then
      For i = 10 To 18
        If Not IsEmpty(Cells(.Row, i)) Then
          bState = False
          Exit For
        End If
      Next
    End If
    With Cells(.Row, "u")
      If bState = True Then
        .Value = "Out of Stock"
      Else
        .Value = "In Stock"
      End If
    End With
  End With
Next
Application.ScreenUpdating = True
End Sub

If your range J:Q holds only numeric data, you could possibly make the code even more efficient by simply summing the values in that range and testing for a non-zero result. For example:

Code:

Sub Test1()
Application.ScreenUpdating = False
Dim LastRow As Long, RngID As Range, RngSum As Range, oCel As Range, i As Long, bState As Boolean
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set RngID = Range("a2:a" & LastRow)
For Each oCel In RngID
  With oCel
    bState = True
    If .Value Like "*FUJ*" Then
      Set RngSum = Worksheets("Sheet1").Range("J" & .Row & ":" & "Q" & .Row)
      If Application.WorksheetFunction.Sum(RngSum) = 0 Then bState = False
    End If
    With Cells(.Row, "u")
      If bState = True Then
        .Value = "Out of Stock"
      Else
        .Value = "In Stock"
      End If
    End With
  End With
Next
Application.ScreenUpdating = True
End Sub

Hwyn · #3 11-14-2011, 11:44 AM

Thank you Paul, I'm actually going to use your test1 solution they both work fine but I find the summing solution easier to follow.
Many Thanks for your kind solution
Robert

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Empty rules	rene.kamphuis@ciz.nl	Mail Merge	21	04-15-2011 12:34 AM
Open source DDE test methods?	d.rock90	Excel Programming	1	01-05-2011 12:03 PM
Test after presentation?	barnkat	PowerPoint	0	08-13-2010 10:58 AM
Count range cells eliminating merge cells	danbenedek	Excel	0	06-15-2010 12:40 AM
how to add test in word document	tekle	Word	2	05-24-2010 01:26 PM