Using "With" Statement with Range Variable

[techfanatic]

Hi Guys,

I'm complete stuck with this. My With statement wouldn't accept my range variable. It keeps on throwing Error 1004. Any help is be appreciated.

If I replace

With Range(findBlankSupRange)

with the code below, it works

With wsData.Range("B2:B" & lastAgentRow)

A portion of the code

    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim findBlankSupRange As Range
    Dim lastAgentRow as Integer

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Data")
    lastAgentRow = wsData.Range("C65536").End(xlUp).Row
    Set findBlankSupRange = wsData.Range("B2:B" & lastAgentRow)
        
    With Range(findBlankSupRange) 'I'm getting error 1004 on this line (Excel 2010)
        Set lastCell = .Cells(.Cells.Count)
    End With

Thank you!

 

[JoeMo]

Hi Guys,

I'm complete stuck with this. My With statement wouldn't accept my range variable. It keeps on throwing Error 1004. Any help is be appreciated.

If I replace

With Range(findBlankSupRange)

with the code below, it works

With wsData.Range("B2:B" & lastAgentRow)

A portion of the code

    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim findBlankSupRange As Range
    Dim lastAgentRow as Integer

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Data")
    lastAgentRow = wsData.Range("C65536").End(xlUp).Row
    Set findBlankSupRange = wsData.Range("B2:B" & lastAgentRow)
        
    With Range(findBlankSupRange) 'I'm getting error 1004 on this line (Excel 2010)
        Set lastCell = .Cells(.Cells.Count)
    End With

Thank you!

You have set the range so don't use Range(findBlankSupRange). Instead use:

With findBlankSupRange
'rest of code

 

[techfanatic]

That explains it. Thanks Joe!

You have set the range so don't use Range(findBlankSupRange). Instead use:

With findBlankSupRange
'rest of code