Excel - 删除行，如果公式返回“” [英] Excel - Delete rows if formula returns ""

问题描述

我使用的论坛是

=IF(E7<30,E7,"")

编辑：更具体地说：

 =IF(E7<30,CONCATENATE(A7,B7,C7,"-",TEXT(D7,"hh:mm:ss"),"-",E7),"")

这让我在288000个空白行中留下了很多空格。我想能够看到所有具有返回值的单元格在一起。而不是滚动所有它们。我已经尝试使用find和replace方法，但是当然这些单元格仍然包含公式，而不是其显示的实际返回值。

This leaves me with a lot of blank rows out of 288000 of them. I would like to be able to see all the cells that have a returned value together. Rather than having to scroll through all of them. I've tried using the find and replace method, but of course the cells still contain the formula, not the actual return value that it displays.

示例。 >

Sample.

15  Mar 2015    00:23:00    100.024         
15  Mar 2015    00:24:00    90.033          
15  Mar 2015    00:25:00    80.142          
15  Mar 2015    00:26:00    70.577          
15  Mar 2015    00:27:00    61.508          
15  Mar 2015    00:28:00    53.056          
15  Mar 2015    00:29:00    45.312          
15  Mar 2015    00:30:00    38.368          
15  Mar 2015    00:31:00    32.347          
15  Mar 2015    00:32:00    27.443          15Mar2015-00:32:00-27.443
15  Mar 2015    00:33:00    23.934          15Mar2015-00:33:00-23.934   
15  Mar 2015    00:34:00    22.117          15Mar2015-00:34:00-22.117
15  Mar 2015    00:35:00    22.111          15Mar2015-00:35:00-22.111
15  Mar 2015    00:36:00    23.695          15Mar2015-00:36:00-23.695
15  Mar 2015    00:37:00    26.43           15Mar2015-00:37:00-26.43
15  Mar 2015    00:38:00    29.895          15Mar2015-00:38:00-29.895

推荐答案

我们可以做一个简短的运行这个例程（它假定你的数据从A2 btw开始）。让我知道。

we can make a short cut to run this routine(it assumes your data starts at A2 btw). Let me know.

Sub Under30()
row1 = 2 ''starting row number
row2 = row1
Do While Cells(row1, 1) <> ""
        If Cells(row1, 5) < 30 Then
            'if e column is less than 30
            myTime = Application.WorksheetFunction.Text(Cells(row1, 4),"hh:mm:ss")
            Cells(row2, 6) = Cells(row1, 1) & Cells(row2, 2) & Cells(row1, 3) & "-" & myTime & "-" & Cells(row1, 5)
            row2 = row2 + 1
        End If
            row1 = row1 + 1
Loop
End Sub

这篇关于Excel - 删除行，如果公式返回“”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！