When you have to process data that is copied from another source such as the Internet or from software in Excel, this data is often not desired. And often the problem of inconsistent data streams and blank alternating lines.
In this case, if the data is small, it can be done manually to delete, but for large data, this method will not be possible because it takes too much time.So is there a way to quickly remove blank lines?Today, Vietnamese Software Channel will introduce to everyone two simple methods to quickly delete blank data lines and below are the ways to do this.
1. Use the available functions
a. Method 1:
- B1: Select all data to be deleted (In case of data many Excel will not allow to select all, then it is mandatory to select each part)
- B2: Press Ctrl + G, a dialog box appears
- B3: Click the Special button
- B4: Click to select Blanks
- B5: Select the Home Tab, click the Delete icon on the Toolbar
- B6: Click Delete Sheet Rows
After performing the above steps, all the blank lines that have been selected will be deleted.
b. Method 2:
- B1: Select all data
- B2: Go to DataFilter Tab
- B3: Uncheck Select All
- B4: Drag the slider to the bottom
- B5: Check (Blanks) and click OK, all blank lines will be filtered
- B6: Select Home Tab, click the Delete icon on the Toolbar
2. Use VBA
- B1: Press ALT + F11 to start the Visual Basic Editor.
- B2: Go to the InserModule menu
- B3: Enter the whole of the following code into the Code edit screen
Sub DeleteBlankRows ()
Dim i As Long
If WorksheetFunction.CountA (Selection) = 0 Then
MsgBox "If you can choose any", vbInformation, "kenhphanmemviet.blogspot.com"
Exit Sub
End If
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA (Selection.Rows (i)) = 0 Then
Selection.Rows (i) .EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
- B4: Return to Excel working screen with Alt + Q combination
- B5: Select all data to delete blank lines
- B6: Select Tab ViewMacrosView Macros
- B7: Select Macro DeleteBlankRows and click Run button
After executing this Macro, all blank lines in the selected data area are automatically deleted.
The above are ways to delete blank data lines, depending on the habits of each person and choose the appropriate way to do it.