1

I got the solution from another thread to create a range of filtered rows:

Dim filterRange
With ActiveWorkbook.Worksheets("SCOPE").Range("A2").CurrentRegion
   filterRange = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlVisible).Address
End With

Now I want to select this range and copy its value into another sheet like this:

Range(filterRange).Select

But I do not understand why this is not working. Can someone explain it?

Thanks in advance.

flubix
  • 49
  • 5
  • You must declare `filterRange As Range` then `Set filterRange` to get it working – Damian Oct 05 '18 at 15:29
  • 1
    No need to `.Select`! Copy `xlVisibleCells`. There are **plenty** of solutions on this site on how to do **exactly** what you are trying to do – urdearboy Oct 05 '18 at 15:29
  • Are you on `SCOPE Sheet` when trying to select that range? If not, the code will throw an error. You will need to activate the `SCOPE Sheet` first. – Subodh Tiwari sktneer Oct 05 '18 at 15:31
  • @Damian I have tried this before and it is not working. The Debugger says " Object is missing" – flubix Oct 05 '18 at 15:33
  • Sorry, forgot to tell you, you won't need the .address part just `Set filterRange = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlVisible)` Butr as @urdearboy said, you should try to not use `Select` cause it may cause troubles. – Damian Oct 05 '18 at 15:35
  • @Damian - setting `filterRange` would not work, because the OP is using `filterRange` as a `String` variable in `Range(filterRange).Select`. – Vityata Oct 05 '18 at 15:37

1 Answers1

0

It does not work only, when there is nothing written in A3.

Thus this part here .Resize(.Rows.Count - 1) returns an error, because it actually is evaluated to .Resize(1 - 1) and 0 is an invalid argument for the Range.Resize Property.

Some way to go around this is:

Sub TestMe()

    Dim filterRange As String

    With ActiveWorkbook.Worksheets("SCOPE").Range("A2").CurrentRegion
        If .Cells.Count <> 1 Then
           filterRange = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlVisible).Address
        Else
           filterRange = .Address
        End If
    End With

    Range(filterRange).Select

End Sub

However, this makes the code look a bit ugly, because it is using .Select and is passing the variable in Range() as a string from .Address. (But if you do not have prejudices and it is Friday in your time-zone - the go for it!)

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Okay, I get your point but this code will select all cells, not only the column "A". Further it only copies the cells displayed on the screen, not the ones underneath it. – flubix Oct 05 '18 at 15:50
  • 2
    @flubix - well, your question is literally *But I do not understand why this is not working*. Concerning the "all cells part", the `CurrentRegion` is the range, which is selected, if you select a given range and press Ctrl+A in Excel. If there are no cells in column B, then it would select only those in column A. Probably you need to define the range, using first and last cell - this is the most robust method. – Vityata Oct 05 '18 at 15:56