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
...
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Ahhh, thanks Alex,

Then need to unprotect like
Code:
fvc
    fvc.Worksheet.Unprotect "YourPasswordHere"  '<--- if password protected.
    Application.Goto fvc
 
Upvote 0
First to acknowlwedge the kind efforts of those offering support ...

This only gives you the row number.
I substituted the original
Code:
fvc.select
with
Code:
ty = ws_ifm.Columns(fvc.Column).Rows(Rows.Count).End(xlUp).Row
ws_ifm.Cells(ty, 10).Select
This did indeed result in a cell being selected in Column J, however, the last cell of the filtered database in column J, occupied or not with a value. The goal is to select the first empty cell in column J of the filtered worksheet.

Ahhh, thanks Alex,

Then need to unprotect like
Code:
fvc
    fvc.Worksheet.Unprotect "YourPasswordHere"  '<--- if password protected.
    Application.Goto fvc

OK, thansk Fugi and Alex. This is close. It did select the "first empty cell", which was incorrectly "J1".
Something is going on that is preventing my code from producing the results with this fix. If I may, share the code again ...

Rich (BB code):
    With ws_dump 'a destination worksheet of copied filtered data to unfiltered
    Stop
        .Rows("2:3").EntireRow.Delete 'removes the two header rows of the master database (ws_ifm)
        mccnt = WorksheetFunction.Count(.Columns("J")) 'check how many titles have been model checked. Column
        If mccnt = f_rowcnt Then
            MsgBox "All model titles have been checked." & Chr(13) & "Proceed to title assessment.", vbExclamation, "Model Checked"
            Exit Sub

        ElseIf mccnt = 0 Then 'model has not been checked yet
            Stop
            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 'abort model checking
            With ws_ifm 'select first empty cell in column J
                Set ifmrng = .Columns(10)
                For Each cell In ifmrng.Cells
                    If cell.EntireRow.Hidden = False Then
                        Set fvc = cell
                        Exit For
                    End If
                Next cell
                If Not fvc Is Nothing Then
                    ws_ifm.Activate
                    ws_ifm.Unprotect
                    Application.Goto fvc
                    ws_ifm.Unprotect
                Else
                    MsgBox "Error: No visible cells found in filtered range.", , "Error"
                End If
            End With
        
        ElseIf mccnt < f_rowcnt Then 'some titles have been checked
            'Stop
            inclflag = False
            response = MsgBox(txt_model & " has not been completely model checked." & Chr(13) & "Proceed with model check?", vbYesNo, "Model Check Failed")
            If response = vbYes Then ' proceed to IFM to complete checking
                Stop
                With ws_ifm
                    Set ifmrng = .Columns(10)
                    For Each cell In ifmrng.Cells
                        If cell.EntireRow.Hidden = False Then
                            Set fvc = cell
                            Exit For
                        End If
                    Next cell
                    If Not fvc Is Nothing Then
                        ty = ws_ifm.Columns(fvc.Column).Rows(Rows.Count).End(xlUp).Row
                        ws_ifm.Activate
                        ws_ifm.Unprotect
                        Application.Goto fvc
                        ws_ifm.Protect
                        Exit Sub
                    Else
                        MsgBox "Error: No visible cells found in filtered range.", , "Error"
                        Exit Sub
                    End If
                End With
            
            Else ' skip updating of model check and proceed with missing titles chec

Imagine the structure of my data in the worksheet (ws_ifm)
Rows 1 and 2 are header rows. Everything below static row 2 is filtered data. Row 1's cells appear empty, but each holds a value for the purposes of not being empty, but the font is white to mask the values. Cell J1 for example has the value 777, the cell font color is white.

The code in purple steps through all the cells in column 10 (J) looking for the first empty cell. This will be the cell to be selected with the code in blue. The issue with this code currently, is it ALWAYS stops at J1. There may be no empty cells, or 100s of empty cells, in column J of the filtered data portion of the worksheet, but the first (if any) is never found because J1 is always identified first. This becomes problematic if within the filtered data there are no empty cells needing to be selected in column J.

I am unsure why J1 is being identified as being empty and triggering
Code:
If cell.EntireRow.Hidden = False Then
as it's not. It has a value of 777. I suspect I can overcome this problem by adjusting the range for ifmrng (
Code:
Set ifmrng = .Columns(10)
) to just that range of displayed rows. J may have empty cells, but cells in column A will always have a value (f_rowcnt). I'm not sure how to define the range of a dynamic database of uncomsecutive rows (filtered data). In my testing for example, the first row of filtered data is 16, the last being 7247 with 5 rows in between.
 
Upvote 0
If you want "Empty" cell then
Rich (BB code):
                For Each cell In ifmrng.Cells
                    If cell.EntireRow.Hidden = False And cell = "" Then
                        Set fvc = cell
                        Exit For
                    End If
                Next cell
And second loop as well, BUT you need to set fcv = Nothing before second loop.
 
Upvote 0
Thank you Fugi. I think we're getting closer.
No longer is J1 being selected, and the first empty cell is being selected. However, it's referencing all the rolls, not just the visible ones in the filtered data.
In my testing, row 16 is the first row and J16 is empty. This is what should be selected. However, with the code adjusted as you suggested, J4 is being selected. J4 is empty, but row 4 is not part of the filtered data and is hidden as a rsult.
 
Upvote 0
The code is selecting the blank cell that is only visible.
Is that what you want? or any blank cell even the cell is not visible?
 
Upvote 0
The code is selecting the blank cell that is only visible.
Is that what you want? or any blank cell even the cell is not visible?
Hi Fuji, thanks for your continued support. I only want to choose from the visible rows. The first empty cell in column J of the visible rows of the filtered data.

Right now, it's finding the first empty cell in column from all rows, visible or not. Which I don't understand due to
Code:
 If cell.entirerow.hidden=false and cell = ""
 
Upvote 0
Hummm strange.
Can you just run this code and see if it selects the right cell?
Code:
Sub test()
    Dim fvc As Range
    With Sheets("dump")
        With Intersect(.AutoFilter.Range, .Columns("j"))
            Set fvc = .Find("", , , 1)
            If Not fvc Is Nothing Then
                .Worksheet.Unprotect
                Application.Goto fvc
            End If
        End With
    End With
End Sub
 
Upvote 0
Hi Fuji,
This works! But your testing code refers to the wrong the worksheet. I changed it to the proper worksheet, and it works.

I wonder if this has anything to do with it. (See the red highlights below ... a "With" within a "With" ... is this proper? Perhaps the root of the problem?)

Rich (BB code):
With ws_dump
    Stop
        .Rows("2:3").EntireRow.Delete
        mccnt = WorksheetFunction.Count(.Columns("J")) 'check how many titles have been model checked
        If mccnt = f_rowcnt Then
            MsgBox "All model titles have been checked." & Chr(13) & "Proceed to title assessment.", vbExclamation, "Model Checked"
            Exit Sub
        ElseIf mccnt = 0 Then 'model has not been checked yet
            Stop
            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.Cells
                    If cell.EntireRow.Hidden And cell = "" Then
                        Set fvc = cell
                        Exit For
                    End If
                Next cell
                If Not fvc Is Nothing Then
                    ws_ifm.Activate
                    ws_ifm.Unprotect
                    Application.Goto fvc
                    ws_ifm.Unprotect
                Else
                    MsgBox "Error: No visible cells found in filtered range.", , "Error"
                End If
            End With
        ...
        End if
End With 
 
Upvote 0
You don't need 2nd With statement and Application.GoTo will select the cell directly from even sheet is not active, so no need to Activate.
I don't find anything wrong...
Code:
With ws_dump
    Stop
        .Rows("2:3").EntireRow.Delete
        mccnt = WorksheetFunction.Count(.Columns("J")) 'check how many titles have been model checked
        If mccnt = f_rowcnt Then
            MsgBox "All model titles have been checked." & Chr(13) & "Proceed to title assessment.", vbExclamation, "Model Checked"
            Exit Sub
        ElseIf mccnt = 0 Then 'model has not been checked yet
            Stop
            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
            Set ifmrng = ws_ifm.Columns(10)
            For Each cell In ifmrng.Cells
                If cell.EntireRow.Hidden And cell = "" Then
                    Set fvc = cell
                    Exit For
                End If
            Next cell
            If Not fvc Is Nothing Then
                ws_ifm.Unprotect
                Application.Goto fvc
                ws_ifm.Unprotect
            Else
                MsgBox "Error: No visible cells found in filtered range.", , "Error"
            End If
        ...
        End if
End With
 
Upvote 0
Solution

Forum statistics

Threads
1,225,406
Messages
6,184,800
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