Clear entire rows if cell is blank

[jordanburch]

Hey All,

I am trying to create a macro to clear the rows if a cell is blank. It has to be dynamic because sometimes the cell will or wont be blank. If any cell in the range C8:c17 is blank i want it to clear that entire row. It can clear a range if that makes it faster or easier the range would be A8:J17. Any help would be greatly appreciated!

Thanks,

J

 

[Joe4]

Do you want this to run automatically, or do you want a macro that you can run whenever you want?
If automatically, how are the cells in C8:C17 being updated?

 

[Fluff]

Are the cells actually empty, or do they have a formula that returns ""

 

[jordanburch]

i want to run it whenever I want I will call it when the user clicks a button.

The cells are actually empty. They do have a dropdown box that the user has to select a value and then it inputs that value if that makes any difference to you.

J

 

[Joe4]

Here is one way, though I wonder if there may be a faster way to do it using Special Cells:

Sub MyClearRows()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each cell In Range("C8:C17")
        If cell = "" Then Rows(cell.Row).ClearContents
    Next cell
    
    Application.ScreenUpdating = True
        
End Sub

 

[Fluff]

Ok, how about

Sub jordanburch()
   Range("C8:C17").SpecialCells(xlBlanks).EntireRow.Value = ""
End Sub

 

[jordanburch]

Thanks you guys! they both worked perfectly. I appreciate your help. I really need to learn how to do these easy ones on my own.

J

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Ok, how about

Sub jordanburch()
Range("C8:C17").SpecialCells(xlBlanks).EntireRow.Value = ""
End Sub

Well, that was easier than I thought! I was trying to work out how to get from the SpecialCells to the entire rows, and making it much harder than it needed to be!
Soemtimes, we overlook the obvious!

 

[jordanburch]

well at least you know how to complete it! much more ahead of myself. I can modify little things here and there but its difficult for me to write this from scratch!