Does a worksheet have to be activated or selected to capture the visible filtered range?

[sspatriots]

Hi,

I have the bit of code below that is referring to a range of filtered cells on another worksheet. I need to know if there is a way of setting this range without having to select or activate the worksheet called "LogDetails". Right now the value for the "Rng" comes up as nothing and I can't see why it would. If I let the code show the worksheet called "LogDetails" it will select the filtered range on that worksheet. Any help, much appreciated.


Thanks, SS

    LR = Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("LogDetails").Range("G1:N" & LR).SpecialCells(xlCellTypeVisible).Select
  
    Set Rng = Sheets("LogDetails").Range("G1:N" & LR).Selection.SpecialCells(xlCellTypeVisible).Select
    
    On Error GoTo 0

    If Rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

 

[6StringJazzer]

Remove this line completely

    Sheets("LogDetails").Range("G1:N" & LR).SpecialCells(xlCellTypeVisible).Select

And remove the Select from this line. It's not needed to assign the range, and it will prevent it from working if the sheet is hidden.

    Set Rng = Sheets("LogDetails").Range("G1:N" & LR).Selection.SpecialCells(xlCellTypeVisible).Select

 

[MARK858]

...and remove the Selection in the middle

Set Rng = Sheets("LogDetails").Range("G1:N" & LR).Selection.SpecialCells(xlCellTypeVisible).Select

 

[6StringJazzer]

...and remove the Selection in the middle

A twofer!

 

[sspatriots]

...and remove the Selection in the middle

Set Rng = Sheets("LogDetails").Range("G1:N" & LR).Selection.SpecialCells(xlCellTypeVisible).Select

This worked great along with the suggestion to remove the Post #4. Thank you all.