从范围(对象)中删除单元格 [英] Remove cell from Range (object)
本文介绍了从范围(对象)中删除单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
背景
我的代码在范围上进行了一些循环,但是,每个交互都应在除刚刚执行的单元格之外的范围内执行.我认为更简单的方法是从存储的范围中删除该单元格.
问题
我无法找到一种方法从存储的对象中删除单元格
代码
这个问题很笼统,但是就这个问题而言
Background
My code does some loops over ranges, however, each interaction should be performed in the range excluding the cell just performed. I think the easier way to do so is to remove the cell from the stored range.
Problem
I have not been able to find a way to remove cell from the stored object
Code
The question is general but, for the matters it would be something like
Sub Sample()
Dim RangeToAnalyze As Range
Dim CounterRange As Long
Dim ExcludeCell As Range 'sample on what is desired to achieve
Set RangeToAnalyze = Selection 'this is based on some other criteria but, in order to reproduce it easier that's why selection
For CounterRange = 1 To 5
Set ExcludeCell = RangeToAnalyze.Find("text")
'now here I would like to find the next cell, but it should exclude the first one in order to go to the next one
Set RangeToAnalyze = RangeToAnalyze.Exclude(ExcludeCell) 'this is what I want to do, so when looping it could jump to the next find (This function is "sample" this is what I am looking to do
Next CounterRange
End Sub
推荐答案
一种方法可能是
Function getExcluded(ByVal rngMain As Range, rngExc As Range) As Range
Dim rngTemp As Range
Dim rng As Range
Set rngTemp = rngMain
Set rngMain = Nothing
For Each rng In rngTemp
If rng.Address <> rngExc.Address Then
If rngMain Is Nothing Then
Set rngMain = rng
Else
Set rngMain = Union(rngMain, rng)
End If
End If
Next
Set getExcluded = rngMain
End Function
测试功能
Sub test()
MsgBox getExcluded(Range("A1:M10000"), Range("a10")).Address
End Sub
这篇关于从范围(对象)中删除单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文