Check or selected rows in array?

[Simon Lloyd]

Hi all, if i have (or can have) an Array of row addresses found like this
[VBA] rFAddr = Array(Selection.Address)[/VBA] where the selection is multiple rows how can i check to see if the row i am currently looking at falls within the array?
[VBA]
rFAddr = Array(Selection.Address)
For rRow = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
If Rows(rRow).Address = rFAdd Then
Rows(rRow).EntireRow.Delete
End If
Next rRow
[/VBA]I want to be able to delete each row that doesn't appear in rFAdd.

[Bob Phillips]

[vba]

Dim rRow As Long

Application.ScreenUpdating = False
For rRow = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1

If Intersect(Rows(rRow), Selection) Is Nothing Then

Rows(rRow).Delete
End If
Next rRow
Application.ScreenUpdating = True
[/vba]

[Simon Lloyd]

Lol! nice to see i was on the right track Bob!, i understand what you have done there with the intersect but was there a way to do it as i intended checking a non contiguos range or array?

[Bob Phillips]

That works fine with a non-contiguous array.

The thing is you didn't have an array, because selection.address is a string.

[Simon Lloyd]

I understand it was a string, originally i used it like: Range(Selection.Address) i was of course trying to be too clever for my own good! firstly i was trying to invert the selection in order to delete all the rows in one go but no matter how i tried it wasn't possible (well for me anyway) so then i thought about having the selected row numbers in an array Selection.Rows but that was a hairbrained idea too - i was probably trying to over complicate things!

[Bob Phillips]

you mean like this

[vba]

Sub DeleteRows()
Dim rng As Range

With ActiveSheet

Set rng = NotUnion(.UsedRange, Selection.EntireRow)
If Not rng Is Nothing Then

rng.EntireRow.Delete
End If
End With

End Sub


'-----------------------------------------------------------------
Function NotUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set NotUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function
[/vba]

[Bob Phillips]

... so then i thought about having the selected row numbers in an array Selection.Rows ...

I had a similar idea on a post a few days ago, and then I remembered Intersect

[Simon Lloyd]

I think the Function way would be a bit clumsier for following and i handn't thought about using specialcells (although i've been quoting them a lot lately!) in my head i imagined a much simpler version of checking an array of numbers generated from the selected rows, my thoughts originally were how to collect the row numbers when the selection is made by the range union, the code i used is below with the intersect method you provided at the bottom.

[VBA]

Sub Find_N_Select_Column_D()
Dim rFind As Range
Dim rValue As String
Dim rFound As Range
Dim rRange As Range
Dim strFirstAddress As String
Dim rRow As Long
Dim rFAddr 'As Range

Set rRange = Range("D10Z130")
rValue = "Test"
With rRange
Set rFind = .Find(rValue, LookIn:=xlValues, lookat:=xlWhole)
If Not rFind Is Nothing Then
strFirstAddress = rFind.Address
Set rFound = rFind
Do
Set rFound = Union(rFound, rFind)
Set rFind = .FindNext(rFind)
Loop While Not rFind Is Nothing And rFind.Address <> strFirstAddress
End If
End With

If Not rFound Is Nothing Then
rFound.EntireRow.Select
End If
Application.ScreenUpdating = False
For rRow = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1

If Intersect(Rows(rRow), Selection) Is Nothing Then

Rows(rRow).Delete
End If
Next rRow
Application.ScreenUpdating = True

End Sub

[/VBA]

[mikerickson]

[VBA]For rowNum = max To min Step -1
If Application.Intersect(Cells(rowNum,1),Range(Join(rfAddr,",")).EntireRow) Is Nothing Then
Rows(rowNum).Delete
End If
Next rowNum[/VBA]