CODEWITHSUNDEEP
excelvba
michiganman75
michiganman75

Reputation: 3

How to check multiple cells one at a time VBA

I am using the code below for a graph. I want to check each cell in the Range("C9:AG9") separately then depending on if that cell IsEmpty = True then it ClearContents of the corresponding Column (Rows 5:8). What is the best way to write this code to have it check each cell independently instead of writing this 31 times for each column?

If IsEmpty(Worksheets("sheet1").Range("C9").Value) = True Then
Worksheets("Sheet1").Range("C5:C8").ClearContents
End If

Upvotes: 0

Views: 593

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

Fast solution without for loop

Sub ClearAtEmpty()
    Dim empty_cells As Range
    With Worksheets("sheet1")
        On Error Resume Next    'switch off error trapping (if no empty cells in the range, the next line will raise the error)
        Set empty_cells = .Range("C9:AG9").SpecialCells(xlCellTypeBlanks)   'get all the empty cells in the range
        On Error GoTo 0         ' switch on error trapping
        If Not empty_cells Is Nothing Then  ' if there are empty cells
            Intersect(empty_cells.EntireColumn, .Rows("5:8")).ClearContents
        End If
    End With
End Sub

Short option (...but not so clear and masking errors)

Sub ClearAtEmptyShort()
    On Error Resume Next    'switch off error trapping (if no empty cells in the range, the next line will raise the error)
    Intersect(Worksheets("sheet1").Range("C9:AG9").SpecialCells(xlCellTypeBlanks).EntireColumn, Worksheets("sheet1").Rows("5:8")).ClearContents
End Sub

Upvotes: 2

user13770736
user13770736

Reputation: 

Dim i as Integer

for i = 0 to 30 'loop certain number of times
    'check if nullstring / also can do isempty function
    if Worksheets("sheet1").Range("C9").Offset(0,i) = vbnullstring then 
         'delete on offset
         Worksheets("Sheet1").Range("C5:C8").Offset(0,i).ClearContents
    end if
next i

you could also use foreach but i think limited for is better solution in this case.

Upvotes: 0

Related Questions