HomeApplicationOffice information

Summary of ways to delete blank lines in Excel 2007, 2010, 2013

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.

Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 1
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
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 2
- B3: Click the Special button
- B4: Click to select Blanks
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 3
- B5: Select the Home Tab, click the Delete icon on the Toolbar
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 4
- 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
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 5
- B3: Uncheck Select All
- B4: Drag the slider to the bottom
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 6
- B5: Check (Blanks) and click OK, all blank lines will be filtered
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 7
- B6: Select Home Tab, click the Delete icon on the Toolbar
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 8
2. Use VBA
- B1: Press ALT + F11 to start the Visual Basic Editor.
- B2: Go to the InserModule menu
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 9
- 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
Summary of ways to delete blank lines in Excel 2007, 2010, 2013 Picture 10
- 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.
4 ★ | 1 Vote
Office informaticstips in Excel

You should read it

May be interested

  • Keyboard shortcut in Excel, how to delete lines in Excel with keyboard shortcutsKeyboard shortcut in Excel, how to delete lines in Excel with keyboard shortcuts
    keyboard shortcut in excel, how to delete lines in excel with keyboard shortcuts. normally, when working with excel if you want to delete a certain line, you will select all the rows then right-click and select delete. but instead of manipulating the mouse you have t
  • Excel with hidden lines - How to hide lines and display hidden lines in ExcelExcel with hidden lines - How to hide lines and display hidden lines in Excel
    excel with hidden lines - how to hide lines and display hidden lines in excel. sometimes in the process of working, processing data in excel, you need to hide / show the line in excel. if you do not know how to hide lines and display hidden lines in excel, please refer to article vi
  • How to delete blank pages in WordHow to delete blank pages in Word
    in the process of writing documents, sometimes you accidentally create blank pages, redundant pages that you do not know why it appears. the following article shows you how to delete blank pages in word.
  • Instructions on how to delete gridlines in ExcelInstructions on how to delete gridlines in Excel
    instructions on how to delete gridlines in excel. the excel interface is a spreadsheet with extended rows and columns. normally in an excel table presentation, you will want to remove the lines that are outside your worksheet array but don't know how to do it quickly.
  • Extremely simple instructions on how to fill in blank cells with 0 in ExcelExtremely simple instructions on how to fill in blank cells with 0 in Excel
    when working in excel, you may encounter a situation where your spreadsheet has many blank cells and need to fill in values ​​in those cells. don't worry, read the article below to learn how to fill in blank cells with 0 in excel.
  • How to draw straight lines and arrows in Excel - Draw shapes in ExcelHow to draw straight lines and arrows in Excel - Draw shapes in Excel
    there are many different ways to draw straight lines and arrows in excel. lines and arrows in excel are often used to draw mind maps in excel or graph functions in excel.
  • How to delete redundant, blank pages in WordHow to delete redundant, blank pages in Word
    how to delete redundant, blank pages in word. in the process of using ms word to edit documents, you will probably encounter the case of accidentally creating redundant, blank pages. it is annoying and makes text incomplete, so contacts
  • Spacing lines in ExcelSpacing lines in Excel
    spacing lines in excel. in the process of editing excel file, sometimes the lines are not spaced as expected or the lines are not spaced evenly. so you want to evenly line the data in excel. the following article will guide fish
  • MS Excel 2007 - Lesson 5: Edit WorksheetMS Excel 2007 - Lesson 5: Edit Worksheet
    editing a spreadsheet in excel 2007 includes operations like: inserting cells, inserting rows / lines and inserting columns, deleting rows, deleting cells, deleting columns, finding data, replacing data or checking spelling, go to a certain cell in the spreadsheet.
  • How to Remove a Blank Page in WordHow to Remove a Blank Page in Word
    extra blank pages in microsoft word are usually the result of extra paragraphs or page breaks. if you've tried (and failed) to delete a blank page by clicking on the bottom-right corner of a blank page and hitting backspace until it...

Office information