Finding First Empty Cell

alring · Oct 11th 2006

Hi

I want to use VBA to find the first empty cell in a colum. But I want VBA to start look for this empty cell after cell B10 and not from the top of the colum. I also want that the VBA stop looking after cell B20. The last part I already have (see below) but I don't know how to have VBA to start the search in cell B10 and not in the top.

Code

Range("B20").End(xlUp).Offset(1#).Value = Range("C1").Value

This is what i have so fare. Can someone please help me with my question?

Thank you in advance.

Alring

pangolin · Oct 11th 2006

Re: Finding First Empty Cell

see if this example code helps

Code

Sub findemt()
Dim mycell As Range
For x = 1 To 11
Set mycell = Sheets("sheet1").Range("B9").Offset(x, 0)
If IsEmpty(mycell.Value) = True Then
mycell.Interior.ColorIndex = 5
Exit Sub
End If
Next x
End Sub

**Krishnakumar** · Oct 11th 2006

Re: Finding First Empty Cell

Hi,

[vba]Dim eCell As Range
With Range("B10:B20")
Set eCell = .SpecialCells(xlCellTypeBlanks).Cells(1, 1)
MsgBox eCell.Address
End With[/vba]

HTH

alring · Oct 11th 2006

Re: Finding First Empty Cell

Hi pangolin

Thank you for the help. It works perfect when I changed the macro a little bit. I wanted that the value from cell C1 was going to be wroted in the empty cell. So I changed the macro to:

Code

Dim mycell As Range
    For x = 1 To 11
        Set mycell = Sheets("sheet1").Range("B9").Offset(x, 0)
        If IsEmpty(mycell.Value) = True Then
            mycell = Range("C1").Value
            Exit Sub
        End If
    Next x

Krishnakumar: Because I wantet to have the value from Cell C1 then I can't use your code. But thank you for your time anyway.

Alring

**Krishnakumar** · Oct 11th 2006

Re: Finding First Empty Cell

Quote from alring

Krishnakumar: Because I wantet to have the value from Cell C1 then I can't use your code. But thank you for your time anyway.

Alring

[vba]With Range("B10:B20")
.SpecialCells(xlCellTypeBlanks).Cells(1, 1) = [c1].Value
End With[/vba]

HTH

alring · Oct 11th 2006

Re: Finding First Empty Cell

Hi Krishnakumar

See that one was a very good one.

Thank you for that one. I will use that one.

Thank you.

Alring

Finding First Empty Cell

Participate now!

Share