VBA SpecialCells code works on home pc, but not on work pc

Question

I write macros to automate workflow at work. My current macro must select the first visible row of filtered data. I tested this code at home on the same version of Excel (16), and it worked fine. However, it gives me [Run-time error code '1004': Unable to get the SpecialCells property of the Range class] when I try it at work.

I have checked to make sure the formatting is the same between books. The data size of the testing book is larger than the at-work book. Since the code works at home with the same version of Excel, I am not sure what could be causing the discrepancy. This is the code that I am using.

Sub First_Visible_Cell()

    With Worksheets("Items").AutoFilter.Range
        Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With

End Sub

Two things **1.** Are you sure that `Worksheets("Items")` is active? You should always fully qualify the range. currently the active sheet may not be the sheet you think it is. Try `Worksheets("Items").Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select` [Interesting read](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) **2.** When using `SpecialCells` it is better to do proper error handling. See [THIS](https://stackoverflow.com/questions/55160529/is-it-good-practice-to-use-range-specialcells-together-with-an-error-handler) example. — Siddharth Rout, Aug 31 '20 at 13:51
@SiddharthRout: Thanks for the 2nd link. I would have handled it with `If Err.Number <> 0 Then` which seems inferior. Another lesson learned (codename `Sandwhich`). — VBasic2008, Aug 31 '20 at 15:22
@Siddharth Rout: I am sure that `Worksheets("Items")` is active. The larger macro specifies the workbook and worksheet. Also, the view ends on the appropriate sheet. It just fails to select a row. However, I did try the suggested alteration of the code to no avail. The real question that I am asking is, "Why do these codes work on my home pc, but not on my laptop logged into my work's virtual desktop? — Garrett Bremer, Aug 31 '20 at 17:04
The above code should work on another pc if it works on your home PC (Under the same scenario). Can you run these few tests for me. Add two lines before the `xlCellTypeVisible` line... `Msgbox Activesheet.name` and then `Msgbox activesheet.autofiltermode`. What messages do you get? — Siddharth Rout, Aug 31 '20 at 17:23
@Siddharth Rout: I added the lines. It gave a Msgbox box that showed the name of the sheet, then one that said "True", then the normal error msg came up. — Garrett Bremer, Aug 31 '20 at 18:51

score 0 · Accepted Answer · answered Aug 31 '20 at 13:52

0

Your Range object is not using the With statement because it is not starting with a dot.

So this

With Worksheets("Items").AutoFilter.Range
    Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With

is actually the same as

With Worksheets("Items").AutoFilter.Range
    ActiveSheet.Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With

And you probably meant to do

With Worksheets("Items").AutoFilter.Range
    Worksheets("Items").Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With

Also note that if there is no AutoFilter set, it will throw an error, as well as if there are no visible cells in your .Offset(1, 0) then it will throw an error too:

Dim wsItems As Worksheet
Set wsItems = Worksheets("Items")

With wsItems.AutoFilter.Range
    Dim VisibleCells As Range
    On Error Resume Next 'hide errors if they occur
    Set VisibleCells = .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1) 'throws error if no visible cells
    On Error Goto 0 're-enable error reporting!

    If Not VisibleCells Is Nothing Then
        wsItems.Range("A" & VisibleCells.Row).Select
    Else
        MsgBox "No Visible Cells"
    End If
End With

answered Aug 31 '20 at 13:52

Pᴇʜ

56,719
10
49
73

I have tried many of the suggestions listed in the comments. All of the code alterations suggested work fine on my home pc. However, they yield the same error message when used on my work laptop. The real question that I am asking is, "Why do these codes work on my home pc, but not on my laptop logged into my work's virtual desktop?" I can't help but think that it might not be a problem with the actual code itself, but how the code is interacting with the remote desktop system, or security features, or something like that. – Garrett Bremer Aug 31 '20 at 17:14
Did you test the last code block I posted? This one cannot give the same error you posted in your question. What is the result you get there? – Pᴇʜ Aug 31 '20 at 20:28
That did work. Thanks! It only selected the first visible cell, but I just added `Rows(ActiveCell.Row).Select` after your code to select the whole row. Now the rest of my code works fine. – Garrett Bremer Sep 01 '20 at 12:56
I am still wondering why the original code worked on one sheet, and not another. It seems odd, and I would like to learn the cause of the odd quirk. Thanks all for the help though! – Garrett Bremer Sep 01 '20 at 13:00
Because using `.Select` and `ActiveSheet` is not very reliable (see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). `ActiveSheet` changes whenever a user clicks on another sheet. That means you cannot reliably tell which sheet that is. Avoid using it where ever you can and use full worksheet reference for every range object. – Pᴇʜ Sep 01 '20 at 13:47
Thanks for the tip. I'll read up on it when I get off of work. – Garrett Bremer Sep 01 '20 at 14:48

VBA SpecialCells code works on home pc, but not on work pc

1 Answers1