VBA that can delete a row from a range where there is no text, although the cell has a formula

Question

I’m creating an automated report template that fits onto one page. This report includes bullet points and a chart. Because of a presentation request I need to make sure the chart always sits the same distance under the last bullet point. Unfortunately, there can be 7 bullet points or 2. So automating this has become troublesome.

See image below, but my thinking is:

1. Specify a range (B11:B17)
2. Go to the bottom of that range and work up
3. Find empty cells in that range and delete entire row (issue 1)
4. Update the range variable to accommodate the change (unable to do)
5. Carry out the previous steps
6. Retrieve original range size and re-insert those rows where it should be
7. Go back to my original procedure to populate the bullets and then start at Step 1 again

VBA is something I struggle with, but find myself using more and more. I have searched and tried different ways to create a range and delete empty cells/rows. Some of them were dangerously bad. I’m starting with this simple code below now, but it’s not working:

Set MyFullRange = Range("B11:B17")
MyFullRange.Select
For CellRange = 1 To MyFullRange.Rows.Count
    If MyFullRange(CellRange).End(xlUp).Row = "" Then
        MyFullRange(CellRange).EntireRow.Delete xlShiftUp
    End If
Next CellRange

My issues

1. The bullet rows are populated by referring to another sheet (=ProjectBullet_1, =ProjectBullet_2, etc.). Therefore I’m having trouble recognising the empty cell and deleting the row accordingly.
2. Reinserting the rows so that it’s back to my original template structure. (see possible alternative idea below)
3. The bulleted rows appear in a certain part of the template $B$20:$B:$26. (below and above other populated rows) and previous attempts to delete rows have resulted in other empty rows being deleted.

Would it be easier (especially for step 6) if I simply copied the template sheet, called it something else and carried Steps 1 to 5 on the copy, deleting the copy once I exported it?

I know I need to break this issue down into pieces, but I'm struggling with the first piece.

Answer 1

You can hide the rows instead of deleting them.

I've modified your code to hide the rows:

Public Sub hiderows()
    Dim thisrange As Range
    Set thisrange = ActiveSheet.Range("B11:B17")
    For CellRange = thisrange.Rows.Count + 10 To 11 Step -1
        If ActiveSheet.Cells(CellRange, 2).Value = "" Then
            Rows(CellRange).EntireRow.Hidden = True
        End If
    Next
End Sub

The keypoint is the line If ActiveSheet.Cells(CellRange, 2).Value = "" Then where I check the value of the cell. It reads the value, not the formula.