Find(What:=

[bsquad]

I am sure this is just the dumbest thing I am overlooking, but does anyone have any insight as to why this first one works and the second brings back a error 91 Object variable or with block variable not set. Both my variables or 'Set' are declared as Ranges - hence having a .Row and a .Column in the MsgBox

            Application.FindFormat.Clear
            Application.FindFormat.Interior.Color = RGB(216, 219, 216)
    Set zRGB216 = ActiveSheet.Columns("A:AA").Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, _
                                                            SearchFormat:=True)
MsgBox (zRGB216.Row)

            Application.FindFormat.Clear
            Application.FindFormat.Borders(xlEdgeRight).Weight = xlMedium
    Set xlBorder = ActiveSheet.Columns("A:AA").Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=True)
MsgBox (xlBorder.Columns)

 

[MARK858]

What happens if you remove the s from Columns?

 

[Rick Rothstein]

I am sure this is just the dumbest thing I am overlooking, but does anyone have any insight as to why this first one works and the second brings back a error 91 Object variable or with block variable not set. Both my variables or 'Set' are declared as Ranges - hence having a .Row and a .Column in the MsgBox

            Application.FindFormat.Clear
            Application.FindFormat.Borders(xlEdgeRight).Weight = xlMedium
    Set xlBorder = ActiveSheet.Columns("A:AA").Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=True)
MsgBox (xlBorder.Column[B][COLOR="#FF0000"][SIZE=4]s[/SIZE][/COLOR][/B])

Try removing the red highlighted "s"

 

[bsquad]

thank you for the responses;

to answer both of you, i get the same error for each. If there is an alternative way I am open to that as well. I would just like to avoid using SpecialCells(xlLastCell) and UsedRange - just because as soon as a user almost even touches a cell outside the desired area it changes both.

 

[Fluff]

Not sure that FindFormat works with borders, but try this

    Dim Usdrws As Long
    Dim UsdCols As Long

    Usdrws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    UsdCols = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

 

[bsquad]

Fluff I think your UsdCols will work, I appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Rick Rothstein]

Not sure that FindFormat works with borders, but try this

    Dim Usdrws As Long
    Dim UsdCols As Long

    Usdrws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    UsdCols = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Fluff I think your UsdCols will work, I appreciate it!

Do you mean the cells having a right border was not a requirement? You only wanted to find the last used cell?

 

[bsquad]

Hi Rick, no having a border was a requirement; I did not want to use UsedRange, its likely users will mess with cells outside of the the visible used range and will end up deleting it. If you had any further input I am open.

 

[bsquad]

I am getting UsedRange.Rows.Count and SpecialCells(xlCellTypeLastCell).Row mixed up with each other, so I think I will be ok using UsedRange without FindFormat / Find, but if anyone has additional ways to use FindFormat / Find with Borders I am open to hear it.