RunTIme Error 13 when trying to find last column with data in specific range.

[Ochimus]

Need to find the last cell containing data in any row between Col D and the column before the one headed "Revision reviewed" (Col P in this example, but has to be "variable" as extra cols will be added).

Code below throws a "RUN TIME ERROR 13" at that point.

Without it, cannot determine the ranges to check and decide whether a Column should be hidden or not.

Spreadsheet attached shows the "Start" position in Cols B - Q, and Cols S - Z show what the end result should be.

Option Explicit
Dim d As Long, f As Long, q As Long, x As Long
Dim SelRange As Range, rng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'HIDE unused Date cells for Patient Number selected

    With Sheet1

'Find the last row
    f = .Cells(.Rows.Count, "B").End(xlUp).Row
    If f < 7 Then f = 7

'Set range to check as Col B ("Patient Number")
    Set SelRange = .Range(.Cells(7, 2), .Cells(f, 2))
    
'Check if active cell is in the Date range
    If Not Intersect(SelRange, Target) Is Nothing Then
    
'Exit sub if cell is blank
    If Target.Value = "" Then
    Exit Sub

    Else:
'Set the row you are clicking
    x = ActiveCell.Row
    
'Find "Revision reviewed" header and go to column before it

    d = Application.Match("Revision reviewed", .Range("6:6"), 0)
    
    
'*****THROWS RUN TIME ERROR 13*******
    
'Find the last column with data in any row in Date range
    q = .Range(.Cells(7, 4), .Cells(f, d - 1)).Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    
 'HIDE subsequent blank columns after the last date
    .Columns(q + 1, d - 1).EntireColumn.Hidden = True
    
'Then HIDE blank columns in Active row
    For Each rng In .Range(.Cells(x, 4), .Cells(x, q - 1))
    If rng.Value = "" Then
    rng.EntireColumn.Hidden = True
    End If
    Next rng
    
    End If
    
 End If
 
 End With
 
End Sub

All solutions, suggestions and alternatives welcome as ever.

Ochimus

[protonLeah]

Dim q As Variant
'Find the last column with data in any row in Date range
 Set q     = .Range(.Cells(7, 4), .Cells(f, d - 1)).Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

 'HIDE subsequent blank columns after the last date
    If Not q Is Nothing Then
        .Columns(q.Column  + 1).Resize(d - q.Column).EntireColumn.Hidden = True
    End If

[Ochimus]

Ben,

Many thanks for prompt response.

Had to tweak your concept slightly, but following Code snippet now hides all columns correctly.

Dim q As Variant
'Find the last column with data in any row in Date range
    Set q = .Range(.Cells(7, 4), .Cells(f, d - 1)).Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

 'HIDE subsequent blank columns after the last date
    If Not q Is Nothing Then
        .Range(.Columns(q.Column + 2), Columns(d - 1)).EntireColumn.Hidden = True
    p = q.Column
    
    End If
    
'Then HIDE blank columns in Active row
    For Each rng In .Range(.Cells(x, 4), .Cells(x, p - 1))
    
    If rng.Value = "" Then
    rng.EntireColumn.Hidden = True
    End If
    Next rng
    
 End If

Thanks again.

Can now mark this as Solved

Ochimus