Removing Blank Cells

[econnell]

Hello there!

I am trying to write a macro that will remove cell rows that are empty after pressing a form control button. The cells that are 'empty' have an IF formula that returns blank "" if a certain criteria is not met, these are the cell rows I want to remove.

I've tried the following code but it deletes all cell rows, even when the cell returns an actual value.

Sub Button4_Click()
Dim sh As Worksheet, rngForm As Range, rngDel As Range, cF As Range
 
 Set sh = ActiveSheet
 On Error Resume Next
  Set rngForm = sh.UsedRange.SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0
 If rngForm Is Nothing Then Exit Sub
 For Each cF In rngForm.Cells      'iterate between cells having formula
    If cF.Value = "" Then          'find the ones returning the null strinig
        If rngDel Is Nothing Then  'if rngDel not Set:
            Set rngDel = cF        'Set it as first such a cell
        Else
            Set rngDel = Union(rngDel, cF) 'make a Union between the existing range and the cell returning a null string
        End If
    End If
 Next
 If Not rngDel Is Nothing Then rngDel.ClearContents  'it will clear the formula
End Sub

I've also tried some along the lines of the following code, which doesn't delete the rows because they aren't technically blank, there is a formula in them.

 

[Michael M]

Maybe this way then.

Sub delRows()
Dim i As Long
    For i = UsedRange.Rows(.Rows.Count).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
    Next i
end sub

 

[Peter_SSs]

What you are describing and showing in your image makes me think that you are no longer using Excel 2016 as shown in your profile.
Your image shows cell B10 as the active cell but it looks like you may have a dynamic array formula in cell B8.
Assuming your version is not 2016 but a more recent version, try a formula like this in B8 and you may not need any vba code at all.
(Also please update your profile Excel version if it has changed)

=LET(d,IF(DataTable[UOM/TAG]="MIL",Data!$B19,""),FILTER(d,d<>"",""))

Two other suggestions/requests for the future:

1. Investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
   (If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)
   
   
2. When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.