Hello,
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.
Hello,
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.
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
' ===============================================================
' CREATE A BACKUP COPY OF YOUR DATA BEFORE RUNNING THIS PROCEDURE
' ===============================================================
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
.Rows(R).Delete
' 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.