VBA Error In Selecting A Cell In A Filtered Worksheet

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,641
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
With the code snippet below, I am referencing a filtered worksheet. I am trying to select the first empty cell in column J of this filtered worksheet. The line in red below nets me an error ("Application-defined or object-defined error") with the line is red.

In my testing, the first empty cell in column J of filtered worksheet "ws_ifm" is 37. J37 is the cell that should be selected (ie have the bounding box around it). When I debug ?cell.address(fvc) I get $J:$J.

Rich (BB code):
Dim mccnt As Long
Dim response
Dim ws_ifm as Worksheet
Dim ifmrng as Range
Dim cell as Range
Dim fvc as Range

Set ws_ifm = wb_catalogue.worksheets("IFM (M)")

With Worksheets("Dump")
        .Rows(2).EntireRow.Delete
        mccnt = WorksheetFunction.Count(.Columns("J")) 'check how many titles have been model checked
        If mccnt = 0 Then 'model has not been checked yet
            response = MsgBox(txt_model & " has not been model checked." & Chr(13) & "Proceed with model check?", vbYesNo, "Model Check Failed")
            If response = vbNo Then Exit Sub
            With ws_ifm 'select first empty cell in column J
                Set ifmrng = .Columns(10)
                For Each cell In ifmrng
                    If cell.EntireRow.Hidden = False Then
                        Set fvc = cell
                        Exit For
                    End If
                Next cell
                If Not fvc Is Nothing Then
                    .range(fvc).Select
                Else
                    MsgBox "Error: No visible cells found in filtered range.", , "Error"
                End If
            End With
...
 
Thank you so much Fuji. I'm not sure how much bearing removing the With ... End If block had on rresolving the issue, but it appears to be working as hoped now.
I appreciate the help of the kind and patient folks like you here at Mr. Excel.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,225,406
Messages
6,184,798
Members
453,259
Latest member
Major_Havoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top