Howto Find LAST empty CELL in RANGE

[gasma1975]

Hello,

I want to find the LAST empty cell in a range. Example:

Let say that no values are in A1 to A7, I have some values in A8 up to A20. If I run the function. It should search within RANGE A and will tell me that A21 is available.

How ?

[zaza]

Welcome to the Forums.

Do you want to do this through VBA or as an Excel function? The two will be quite different. Also, can you be more specific about what you mean by "Range". In your example, the last empty cell in range A is A65000. If, however, you specify the range to be A1:A21, then indeed it will be A21.

Through VBA code, you would just loop:

For i = 21 to 1 step -1
  if range("A" & i) = "" then
   rownumber = i
   exit for
  end if
 next i 
 
 if i = 1 and range ("A1") <> "" then
 Msgbox "No empty cells"
 else
 MsgBox "Last empty cell is A" & rownumber
 end if

or something of the sort.

zaza

[RobDog888]

Welcome to the Forums.

You can use the .SpecialCells function to do that.

'Row:
MsgBox Workbooks(1).Sheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row
 
'Column:
MsgBox Workbooks(1).Sheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Column

[gasma1975]

Thanks All.

It works fine...

[zaza]

Actually, you want a "+1" on the end of that Row RD...






Forgot about SpecialCells. Nice one.

[RobDog888]

It gives the last "used" row so its implied.

Thanks

[aikidokid]

Just done a search on this topic.
I have tried using this but it gives me '357' when the last used row at the moment is '16

'Column:
MsgBox Workbooks(1).Sheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Column

I have also tried this piece of code but I cannot get it to start from the point I want to, row 8 rather than row 1

Do
        Range)"A8").Select
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
            ctr = ctr + 1
        End If
    Loop Until IsEmpty(ActiveCell) = True

I just want to find the first empty row after row 8, these rows are used for various titles and some are merged.

Any ideas?

[Webtest]

Hey RobDog:

In the following:

MsgBox Workbooks(1).Sheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Column

I believe that "Range("A:A")." is meaningless and non-functional. "Cells.SpecialCells(xlCellTypeLastCell).Column" will always return the index of the last column on the sheet that has data in it.

[Webtest]

Likewise:

MsgBox Workbooks(1).Sheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row

Range("A:A"). is ignored and the actual Row index returned is the row number of the bottom-most, right-most populated cell on the sheet. This has always been an aggravation to me, that you can't sepcify a range.

I think you have do do a reverse iterative test to find the "first empty cell after the last used cell in a range".

To find the last cell in a range:

aRange.Cells(aRange.Cells.Count)

Returns the last cell in range "aRange". Add one to the row or column, and iteratively test backwards from the end looking for empty cells.

[aikidokid]

Thanks All
WebTest:
I think I understand this for the most part, but what exactly is 'aRange'

I just tried this and it didn't work:

Range("A7").Cells (Range("A7").Cells.Count)

I looked up aRange is the help files and found nothing

[aikidokid]

Just tried this as well

Range("A7").Select
aRange.Cells (aRange.Cells.Count)

Sorry if this is obvious!!

[Webtest]

This is the basic idea. If you have multiple Workbooks open or multiple sheets, you'll need to handle those specifically. This works for one sheet in one book.

Option Explicit

Sub Macro1()
    Dim aRow As Long
    Dim aRange As Range
    
    'This is an attempt to find a cell in column A that is guaranteed to be empty
    'Find the row number below the bottom-most occupied cell
    aRow = Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1

    While Cells(aRow, "A").Value = ""
        aRow = aRow - 1
    Wend
    
    'aRange is the first available empty cell below the last used cell in Column A
    Set aRange = Cells(aRow + 1, "A")
    
    MsgBox aRange.Address

End Sub

[aikidokid]

Thanks again Webtest

I have come up with the following, which counts how many rows are used after the rows used in titles, merged rows etc.
It also puts the names in the column into an array so i can check for duplicates.

Apart from the loop going to 1000, I can't see a major problem with this as firstly it will never get to that many names, probably no more that 1 or 2 hundred, and secondly, as soon as an empty cell is found it exits the loop.

Can you see anything wrong with this code

'loop through to find how many rows are used already for array
    Range("A8:A1000").Select
    For ctr = 1 To 1000
        Do While ActiveCell(ctr).Value <> ""
            ReDim Preserve strNames(ctr - 1)
            strNames(UBound(strNames)) = ActiveCell(ctr).Value
            Debug.Print ActiveCell(ctr).Value
            Debug.Print strNames(UBound(strNames))
            ctr = ctr + 1
        Loop
        Exit For
    Next

[Webtest]

ActiveCell returns a single cell as a Range. I would be surprised if using an index "(ctr)" would work at all. Doesn't it generate an error?

ActiveCell(ctr).Value

[aikidokid]

No, no error at all.
When using the debug.print it shows exactly what I expect it to show, both in the activecell.value and in the array element.

[Webtest]

Hmmmmm ... interesting. It sort of works as a 'cheap' row offset function ... stays in the same column, but walks down the rows. Index of "1" is the currently active cell. Indexing a range object seems to work the same way.

[aikidokid]

So you don't see a problem with it as it is then?

[Webtest]

It seems that adding an index to a RangeObject assumes a ".Offset" prefix to the index parentheses. Surprisingly, even the following works ... resulting in "D17".

Range("C9")(9, 2).Address

[Webtest]

For the sake of clarity and compatibility I would explicitly add in the ".Offset(ctr)".

[Webtest]

This is really tricky! Without the explicit "Offset" the offsets are 1 based. With the Offset added explicitly, the offsets are ZERO based!