本文介绍了找价值如果找到,请移动到下一行并删除所有空行,直到找到下一个非空行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



列A

 空白
空白
东西
传真号码:
空白
空白
空白
下一个非空行

我正在使用以下VBA代码在A列中找到我的值传真号码:

  Dim Rng As Range 

设置Rng =范围(A:A)
Rng.Select
设置cell = Selection.Find(What:=Fax Number:,After:= ActiveCell,LookIn:= xlFormulas,LookAt:= xlPart,SearchOrder:= xlByRows,SearchDirection:= xlNext,MatchCase:= False,SearchFormat:= False)

如果cell Is Nothing然后
'做某事
Else
Dim MyRange As Object
设置MyRange = cell.Offset(1,0)。结束(xlDown).Row
MyRange.EntireRow.Delete
结束如果

如果找到,传真号码下面的所有空行都应该被删除,直到下一个非空行。



给出这个结果:

  Co l $ A 
空白
空白
东西
传真号码:
下一个非空行

有人可以告诉我我哪里错了吗?我得到一个对象未​​定义的错误:

设置MyRange = cell.Offset(1,0).End(xlDown).Row 。 p>

解决方案

尝试以下代码,而不使用选择选择

  Option Explicit 

Sub UseFind()

Dim Rng As Range,C As Range

设置Rng =范围(A:A)

设置C = Rng.Find(什么: =Fax Number:,After:= ActiveCell,LookIn:= xlFormulas,LookAt:= xlPart,SearchOrder:= xlByRows,SearchDirection:= xlNext,MatchCase:= False,SearchFormat:= False)

如果C是没有然后
'做一个
Else
Dim LastRow As Long
Dim MyRange As Range
LastRow = C.Offset(1,0).End (xlDown).Row - 1'< - 获取最后一行Blank
设置MyRange =范围(C.Offset(1,0),Cells(LastRow,C.Column))'< - set删除的范围
MyRange.EntireRow.Delete
如果

End Sub


I have a spreadsheet like so:

Column A

Blank
Blank
Something
Fax Number:
Blank
Blank 
Blank
Next non empty row

I am using the following VBA code to find my value 'Fax Number:' in column A:

Dim Rng As Range

Set Rng = Range("A:A")
Rng.Select
Set cell = Selection.Find(What:="Fax Number:", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    'do it something
Else
    Dim MyRange As Object
    Set MyRange = cell.Offset(1, 0).End(xlDown).Row
    MyRange.EntireRow.Delete
End If

If found, all the empty rows below fax number should be deleted until the next non empty row.

Giving this result:

Column A
Blank
Blank
Something
Fax Number:
Next non empty row

Can someone show me where I am going wrong? I get an object not defined error on:
Set MyRange = cell.Offset(1, 0).End(xlDown).Row.

解决方案

Try the code below, without useing Select and Selection.

Option Explicit

Sub UseFind()

Dim Rng As Range, C As Range

Set Rng = Range("A:A")

Set C = Rng.Find(What:="Fax Number:", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If C Is Nothing Then
    'do it something
Else
    Dim LastRow As Long
    Dim MyRange As Range
    LastRow = C.Offset(1, 0).End(xlDown).Row - 1 '<-- get last row of Blank
    Set MyRange = Range(C.Offset(1, 0), Cells(LastRow, C.Column)) '<-- set the deleted range
    MyRange.EntireRow.Delete
End If

End Sub

这篇关于找价值如果找到,请移动到下一行并删除所有空行,直到找到下一个非空行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-10 20:31