How to remove rows after a particular interval



I need to remove rows/cells marked in yellow.

Please suggest any formula to remove these rows/cells so that there should not be any blank row.

Thank You.



Selected Answer

No formula can remove rows. It would be possible to write a formula which copies only selected rows to another sheet. The same effect can be achieved with smaller effort using VBA. The code below doesn't even bother to create a copy. It simply removes the rows you don't want from the sheet you specify.

Sub RemoveBlanks()
    ' 09 Aug 2019
    ' removes blanks and captions

    ' ===============================================================
    ' ===============================================================

    Dim Ws As Worksheet
    Dim Tmp As Variant
    Dim Rl As Long
    Dim R As Long

    ' you can specify another workbook, such as ActiveWorkbook
    ' and/or specify the name of any tab in that workbook
    ' modify as required:-
    Set Ws = ThisWorkbook.Worksheets("fwr")

    ' for greater speed: screen will not change until all is done
    Application.ScreenUpdating = False
    With Ws
        ' presumes that column A is the longest column in Ws
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row

        ' presumes that data start from row 2 (meaning 1 caption row)
        For R = Rl To 2 Step -1
            ' check each cell in column A
            Tmp = .Cells(R, "A").Value
            If (Not IsNumeric(Tmp)) Or (Len(Trim(Tmp)) = 0) Then
                ' it its value isn't a number or the cell is blank, delete the row

                ' print a control message in the Immediate window
                ' disable this line after testing by typing an apostrophe
                ' at its beginning
                Debug.Print "Row "; R; " was deleted"
                ' note that the Immediate window has only 255 rows.
                ' If more rows were deleted only the last 255 will be shown.
            End If
        Next R
    End With

    ' update the changes on the screen
    Application.ScreenUpdating = True
End Sub

The code is demonstrated in the attached workbook which itself is a copy of the one you posted. Look for the code in the TeachExcel module. You can run it with F5 from the VB Editor window or by selecting it in the dialog box that opens when you press the Macros button on the Ribbon's Developer tab.

Observe that the code identifies superfluous rows as those which don't have a number in column A. Rows that don't have a number in column A will be deleted.


Answer the Question

You must create an account to use the forum. Create an Account or Login