How do I get a sub-range in VBA

[MikeMcCollister]

I have a table called "MainChecking". I can get the range for a column of the table by using the .Range function. Now I want a subset of that range but I can't figure it out. For example, this range may have 500 rows in it with just one column. I want to get a range that will be from row 229 to 236 so I can then search within that subset of the range. I can't figure this out. The best that I can figure is with the line with the comment "UGH! does not work". But it does not work, ugh!

Here is a portion of my code.

Private Const gCheckingAccountWorksheet As String = "Main Checking"
Private Const gCategoryRange As String = "MainChecking[Category]" ' table column

With ThisWorkbook.Worksheets(gCheckingAccountWorksheet)
    Dim mRowFirst As Long
    Dim mRowLast As Long
    Dim workingRange As Range
    Dim workingSubRange As Range

    mRowFirst = 229 ' value found elsewhere
    mRowLast = 236 ' value found elsewhere
    Set workingRange = .Range(gCategoryRange)
    Set workingSubRange = workingRange.Range(workingRange.Cells(mRowFirst, 1), workingRange.Cells(mRowLast, 1)) ' UGH! does not work

End With

Does anyone have any ideas on how to get this to work?

Thanks,

Mike

 

[Z51]

Try changing

Set workingSubRange = workingRange.Range(workingRange.Cells(mRowFirst, 1), workingRange.Cells(mRowLast, 1))

to

Set workingSubRange = Range(workingRange.Cells(mRowFirst, 1), workingRange.Cells(mRowLast, 1))

 

[MikeMcCollister]

Thank you very much. That fixed it.