Finding range of first empty cell in column and set as variable's range...

[MistakesWereMade]

I've listed my code below. I want to be able to set the first empty cell's range in a column equal to the range of variable "PasteCell". However, with my code, I get the error "Method 'Range' of object '_Global' failed when trying to msgbox the row location of PasteCell.

Any ideas?

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String
Dim PasteCell As Variant


With Sheets("Inventory")


sourceCol = 7   'column F has a value of 6
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row


    'for every row, find the first blank cell and select it
    For currentRow = 3 To rowCount
        currentRowValue = Cells(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            PasteCell = Sheets("Inventory").Range(currentRow & sourceCol)
        End If
    Next


MsgBox (Range(PasteCell).Row)


End With

 

[MistakesWereMade]

Well, I tried those suggestions, but I just couldn't get it. Finally it took the below code for me to get the desired effect. currentRow was giving me the right row of the first blank cell and I knew what column the blank should be in, so I just did PasteCell = "G" and currentRow and was then able to save the blank cell as PasteCell.

Thanks for helping guys!!!

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String
Dim PasteCell As Variant


With Sheets("Inventory")


sourceCol = 7
rowCount = .Cells(Rows.Count, sourceCol).End(xlUp).Row


    'for every row, find the first blank cell and select it
    For currentRow = 3 To rowCount
        currentRowValue = .Cells(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Set PasteCell = .Cells(currentRow, sourceCol)
        End If
    Next


PasteCell = "G" & currentRow
Sheets("Inventory").Range(PasteCell) = Sheets("Inventory").Range(a).Value

 

[Fluff]

Glad you got it working & thanks for the feedback