Excel中最常见的任务之一是删除空白行。无论您在Excel(Excel)中可能拥有何种类型的数据，在很多情况下，整个文件中都会出现一堆空行。
如果您有数千行，手动删除空白行是一件很痛苦的事情，而且几乎是不可能的。删除Excel(Excel)中空行的最常见方法，至少如网上所述，是使用某种Excel宏。
但是，如果您不熟悉宏，则此方法可能难以实现。此外，如果它不能正常工作，您可能不知道如何更改特定Excel文件的宏。在本文中，我将向您展示几种在Excel中删除空白行的方法以及每种方法的优缺点。请注意，这些方法适用于任何版本的Excel，从Excel 2003一直到Excel 2016 及更高版本。
方法 1 – 添加排序列
幸运的是，有一种简单且非常有效的方法可以在没有任何宏的情况下删除空白行。它基本上涉及排序。这是你如何做到的。假设我们在Excel中有以下数据集，我们想去掉空行：
我们要做的第一件事是插入一列并对其进行连续编号。你可能会问我们为什么要这样做？好吧，如果行的顺序很重要，当我们对 A 列进行排序以去除空白行时，将无法将行恢复为排序前的顺序。
在我们对动物名称列进行排序之前，工作表应如下所示：
现在选择两列并单击Excel中的(Excel)数据(Data)功能区。然后单击“数据”选项卡上的“(Data)排序(Sort)”按钮。
对于排序依据( Sort By)，选择B 列(Column B)，然后单击确定。请注意，您要对其中包含空白值的列进行排序。如果不止一列有空白值，只需选择一列。
现在您的数据应该如下所示。如您所见，删除空白行很容易，因为它们都在底部：
删除行后，现在您可能会看到为什么我们添加了带有数字的列？排序之前的顺序是“猫(Cat)、狗(Dog)、猪(Pig)等”，现在是“熊(Bear)、猫(Cat)、牛(Cow)等……”。因此，只需按 A 列再次排序即可恢复原始订单。
很(Pretty)容易吧？我喜欢这种方法的地方在于，它也很容易删除只有一列具有空白值的行，而不是整行都是空白的。那么，如果您需要检查多列而不仅仅是一列怎么办？
好吧，使用同样的方法，您只需在“排序(Sort)依据”对话框中添加所有列。这是我创建的另一个示例：
如您所见，这张纸更复杂。现在我有几行完全空白，但有几行只是部分空白。如果我只是按 B 列排序，我不会在底部得到所有完全空白的行。添加数字后，选择所有五列，然后单击Sort，您可以看到我在Sort by 选项中添加了四个级别。
当你这样做时，只有我添加的所有四列都是空白的行才会显示在底部。
现在您可以删除这些行，然后继续使用其余的行，使它们恢复到原来的顺序。
方法 2 – 使用过滤器
与使用排序(Sort)功能类似，我们也可以使用过滤(Filter)选项。使用这种方法，您不必添加任何类似的额外列。只需(Just)选择所有需要检查空白的列，然后单击过滤器(Filter)按钮。
您会看到标题行中每个标题旁边出现一个下拉箭头。单击它，然后取消选中全选(Select All)并选中底部的空白。(Blanks)
现在对工作表中的每一列执行相同的操作。请注意，如果您到达只显示空白行的地步，那么您不必为任何剩余的行选择空白，因为默认情况下它已被选中。
您将看到空白行的编号以蓝色突出显示。现在只需选择这些行，右键单击并选择Delete。一旦它们消失，只需再次单击“过滤器(Filter)”按钮以删除过滤器，所有原始的非空白行应该会重新出现。
方法 3 – 删除任何空白
我想提到的另一种方法将删除任何具有完全空白行或任何具有甚至单个空白列的行的行。这绝对不适合所有人，但如果您想删除只有部分数据的行，它可能会很有用。
为此，请选择工作表上的所有数据并按 F5 键。这将打开Go To对话框，您要在其中单击Special。
现在从列表中选择空白(Blanks)，然后单击确定。
您会看到所有空白单元格或行都将以灰色突出显示。现在在Home选项卡上，单击Delete，然后选择Delete Sheet Rows。
如您所见，这将删除任何列中甚至包含一个空白单元格的任何行。
这不是对每个人都有用，但在某些情况下，它也很方便。选择最适合您的方法。享受！
How to Delete Blank Lines in Excel
One of the most cоmmon tasks in Excel is deleting blank rows. Whatever type of data you may have in Excel, there are many occasions where you will also have a bunch of blаnk lines throughout the file.
If you have thousands of rows, manually deleting blank lines is a major pain and virtually impossible. The most common way to delete blank lines in Excel, at least as mentioned online, is to use some sort of Excel macro.
However, if you’re not familiar with macros, this method can be difficult to implement. Also, if it doesn’t work properly, you may have no idea how to change the macro for your particular Excel file. In this article, I’ll show you a couple of ways you can delete blank rows in Excel and the advantages and disadvantages for each method. Note that these methods will work for any version of Excel, from Excel 2003 all the way to Excel 2016 and beyond.
Method 1 – Add Sorting Column
Luckily, there is a simple and very effective way to delete blank lines without any macros. It basically involves sorting. Here’s how you do it. Let’s say we have the following set of data in Excel and we want to get rid of the blank lines:
The first thing we’ll do is insert a column and number it consecutively. You might ask why we would want to do this? Well, if the order of the rows matters, when we sort Column A to get rid of the blank lines, there will be no way to get the rows back in the order they were before sorting.
Here’s what the sheet should look like before we sort the animal name column:
Now select both columns and click on the Data ribbon in Excel. Then click on the Sort button on the Data tab.
For Sort By, choose Column B and then click OK. Note that you want to sort the column that have the blank values in it. If more than one column has blank values, just pick one.
Now your data should look like this below. As you can see, it’s easy to delete the blank rows because they are all at the bottom:
Once you delete the rows, now you can probably see why we added the column with the numbers? Before sorting, the order was “Cat, Dog, Pig, etc..” and now it’s “Bear, Cat, Cow, etc…”. So just sort again by column A to get the original order back.
Pretty easy right? What I like about this method is that it’s also really easy to delete rows that only have one column with blank values rather than the entire row being blank. So, what if you need to check on multiple columns rather than just one?
Well, using this same method, you would just add all the columns in the Sort by dialog. Here’s another example I created:
As you can see, this sheet is more complicated. Now I have several rows that are completely blank, but several rows that are only partially blank. If I just sort by column B, I won’t get all the completely blank rows at the bottom. After adding the numbers, select all five columns, and clicking on Sort, you can see I added four levels to the Sort by option.
When you do this, only the rows where all four of the columns I added are blank will show up at the bottom.
Now you can delete those rows and then go ahead and resort the rest of the rows to get them back into the original order they were in.
Method 2 – Using Filters
In a similar way to using the Sort function, we can also use the Filter option. Using this method, you don’t have to add in any extra column of anything like that. Just select all the columns that need to be checked for blanks and click on the Filter button.
You’ll see a dropdown arrow appear next to each title in the header row. Click on that and then uncheck Select All and check Blanks at the bottom.
Now do the same thing for each column in the worksheet. Note that if you get to a point where there are only blank rows showing, then you don’t have to select blanks for any of the remaining rows as it’s already checked by default.
You will see the number for the blank rows are highlighted in blue. Now just select those rows, right-click and choose Delete. Once they are gone, just click on the Filter button again to remove the filter and all of your original non-blank rows should reappear.
Method 3 – Delete Any Blanks
One other method I wanted to mention will delete any row that has either a completely blank row or any row that has even a single blank column. This is definitely not for everyone, but it could be useful if you want to delete rows that have only partial data.
To do this, select all the data on the sheet and press the F5 key. This will bring up the Go To dialog, where you want to click on Special.
Now select Blanks from the list and click on OK.
You’ll see all the blank cells or rows will be highlighted in grey. Now on the Home tab, click on Delete and then choose Delete Sheet Rows.
As you can see, this deletes any row that has even a single blank cell in any column.
This isn’t useful for everyone, but in some cases, it’s quite handy also. Choose the method that works best for you. Enjoy!
