Delete blank rows between the two rows that contain data

[beginner]

This file in the future will be read-only or as a template.
I need VBA to when saving files (SaveAs) but before saving the macro should delete all blank rows between the two rows containing data.

Can anybody help me?


Please see attach

[beginner]

I have tried this VBA macro

Sub Macro3()
'Select empty rows in range and delete them
    Range("A10:H50").Select 'this range is the differencing
    Selection.SpecialCells(xlCellTypeBlanks).Select 'selecting empty rows
    ActiveWindow.SmallScroll Down:=18 '??????
    Selection.Delete Shift:=xlUp 'search last row with data?
    ActiveWindow.SmallScroll Down:=-27 '????
    Range("A1").Select 'position when VBA is finished
End Sub

But I have a problem when the VBA using the second case.
How to rearrange VBA when I can use for both cases.
How to create a universal range and delete "X?" empty rows that change from case to case.

[gmayor]

The following will work with your examples:

Sub DeleteEmptyRows()
Dim rng As Range
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long
    With ActiveSheet
        LastRow = .Cells(.rows.Count, "G").End(xlUp).Row        'G is the column with the last row of data
        LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column        '10 is the first row of the range
        Set rng = .Range("A10:H" & LastRow)        'Set the range to the area to be processed
        For i = LastRow To 10 Step (-1)        'Process from the bottom
            'remove the empty rows
            If WorksheetFunction.CountA(rng.rows(i)) = 0 Then rng.rows(i).Delete
        Next
    End With
End Sub

[beginner]

@gmayor thank you for answer
I now see that I was wrong from the start. I did not say formulas that are in range.
Therefore, some cells contain formulas that have been copied to the last row in the range. However, I want SaveAs file but before save I want delete the rows that are empty or contain this formula because the formula display zero as a result.
Can you rearrange VBA (please you see attachment file)
Thanks advance

by the way - url link to your website in your signature is incorrect.

[gmayor]

It needs a minor adjustment to account for that :

Sub DeleteEmptyRows()
'Graham Mayor
Dim rng As Range
Dim i As Long
Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row - 4        'G is the column with the last row of data
        Set rng = .Range("A1:D" & LastRow)        'Set the range to the area to be processed
        For i = LastRow To 10 Step -1        'Process from the bottom
            If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then rng.Rows(i).EntireRow.Delete
        Next i
    End With
End Sub

Thanks for the heads up about the link. There was an unwanted space in the link. Now it should be fixed.

[beginner]

Originally Posted by gmayor

It needs a minor adjustment to account for that :

Thank you very much Graham
[problem SOLVED]