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

 

[Fluff]

Try

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


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
            Set PasteCell = .Cells(currentRow, sourceCol)
        End If
    Next


MsgBox PasteCell.Row


End With

 

[DanteAmor]

Try this

Sub test()
    Dim col As Long, lr As Long
    col = 7
    lr = Cells(Rows.Count, col).End(xlUp).Row + 1
    MsgBox "Row : " & Range(Cells(3, col), Cells(lr, col)).Find("").Row

End Sub

 

[MistakesWereMade]

Hi Fluffy,

Thanks again for your quick response. It seems to error out at the msgbox this time with a new error saying "object required".

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
            Set PasteCell = .Cells(currentRow & sourceCol)
        End If
    Next


MsgBox PasteCell.Row


End With

 

[MistakesWereMade]

Hi Dante,

Thanks for the suggestion. Unfortunately, the code keeps returning 3 even though I put something in the third row.

 

[Fluff]

Does your data in Col G contain blank cells below row 2, but before the last row with data?

 

[DanteAmor]

Hi Dante,

Thanks for the suggestion. Unfortunately, the code keeps returning 3 even though I put something in the third row.

Did you put something in cell G3?

 

[MistakesWereMade]

Yes to both, I put something in G3 because my goal is to have code that finds the first empty cell in each column heading from top to bottom. If it finds an empty cell, I would like to store the range so I can fill this empty cell's value. My col G contains its first blank cell in G5. However, sometimes the first blank cell could change to G3 or any cell after G100.

 

[DanteAmor]

Yes to both, I put something in G3 because my goal is to have code that finds the first empty cell in each column heading from top to bottom. If it finds an empty cell, I would like to store the range so I can fill this empty cell's value. My col G contains its first blank cell in G5. However, sometimes the first blank cell could change to G3 or any cell after G100.

The code works for me.
Try this

The code searches from G3 and up to the last row with data in column G (column 7) and shows the first empty cell.

Sub test2()
    Dim c As Long, a As String, r As Long
    c = 7
    a = Range(Cells(3, c), Cells(Cells(Rows.Count, c).End(xlUp)(2).Row, 7)).Address
    r = Evaluate("=MIN(IF(" & a & "="""",ROW(" & a & ")))")
    MsgBox "Row : " & r & " Celda : " & Cells(r, c).Address
End Sub

 

[Fluff]

This line

Set PasteCell = .Cells(currentRow & sourceCol)

should be

Set PasteCell = .Cells(currentRow, sourceCol)