Filter a Range in VBA Excel

[Hermanator]

Hello,

This is what I'm trying to achieve: I have a worksheet with 2 columns of data as follows:

1 A B
2 2 243
3 1 12
4 0 55
5 -1 1
6 -2 23
7 -3 12

I want to perform some calculations on these values, but only on the negative X-values. I can set a range in VBA, that contains all used cells of columns X and Y containing the data (A1:B7). It is ofcourse possible to find the start and end of the range of negative values and create a new range that way (A5:B7), however the negative range is not necessarily continuous.
Is there a way to automatically create a new range, containing only those values with X<0 ? I have tried already to use the "Filters" propery, but that enables the "AutoFilter" function on the worksheet. Is there a way to filter a range using VBA, that leaves the worksheet unaffected?

Thank you

 

[Chance1234]

not sure i quite understand what you want your end result to be,

but you could loop through the whole range, have a if statement to match your condition,

if the condition is matched add the address of that cell to a new range object



Chance,

Filmmaker, gentleman and ROMAN!

 

[Hermanator]

Thank you for your suggestion.

Indeed, that would be one way to solve the problem. However, I think that would be a rather time-consuming way since, in reality the range to be evaluated is quite large. So it has to go trough all cells to check if the value is <0.
I was hoping to find a way to use 1 statement and get the range I need.

 

[xlbo]

Have a look at ADVANCED filter

It can filter in place or export to new location. No filter headers. Only caveat is that you need a couple of cells to reference as the criteria

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chance1234]

However, I think that would be a rather time-consuming way since

Nope, less than a second on 65000 rows

Sub chance()
Dim lngX As Long
Dim strX As String
Dim rngX As Range

Debug.Print Format(Time(), "MM:SSS")


lngX = 65000

Do Until lngX = 1


If Cells(lngX, 2) < 0 Then
    strX = "B" & lngX & "," & strX
End If



lngX = lngX - 1
Loop

strX = Left(strX, Len(strX) - 1)
strX = "=" & strX


Set rngX = Sheet1.Range(strX)



Debug.Print Format(Time(), "MM:SSS")

For Each Cell In rngX
    Debug.Print Cell.Value
    
Next


End Sub

Chance,

Filmmaker, gentleman and ROMAN!

 

[xlbo]

Dunno how you tested that Chance but it fails for me on 58000 rows - too many non contiguous ranges. Also - took 5 seconds to get to the range setting (Set rngX = Sheet1.Range(strX) )

Personally, I would use the ADVANCED filter
Filter in place and then do any testing on the visible rows in that range. The advanced filter takes <1sec to work on over 58000 rows with a random set of +ve and -ve numbers

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chance1234]

must have had less - numbers but good call on the
many non contiguous ranges



Chance,

Filmmaker, gentleman and ROMAN!