Macro for finding an empty cell in a table column, but functions...

[Ferryjaja]

Hi all,

I am looking for a macro that lets me find the first ''empty'' cell in a table column. However, that exact same column contains functions that refer to another tab. Therefore a ''normal'' macro won't let me find that first empty cell, because I think it sees the function as ''something'' instead of nothing. The function in the cells is (='tab'!F16).

For some more info:
Tab is called ''RVO1''
The column I need to find an ''empty'' cell in starts at F12 and ends at F100001.
The table itself is called ''Table73''

Hope I gave you all the information needed.

 

[Legacy 143009]

Sub check()
  Dim lRow As Integer
  For i = 1 to 1000000
    If Cell(i, 6).Value = "" Then
      lRow = i
      Exit For
    End If
  Next

 'Do whatever you want with lRow value
End Sub

 

[Ferryjaja]

Thanks!

However, it gives me the following error: ''sub or function is not defined''.

 

[Legacy 143009]

Sorry for typo:

Sub check()
  Dim lRow As Integer
  For i = 1 to 1000000
    If Cells(i, 6).Value = "" Then
      lRow = i
      Exit For
    End If
  Next

 'Do whatever you want with lRow value
End Sub

 

[Ferryjaja]

No problem
The error is gone, but it does not bring me to the first ''empty'' cell. I can't see what it does do right now.. hmm

 

[Legacy 143009]

You didn't provide what to do with empty cell. lRow is the last row number. Do whatever you want.

Sub check()
  Dim lRow As Integer
  For i = 1 to 1000000
    If Cells(i, 6).Value = "" Then
      lRow = i
      Exit For
    End If
  Next

  MsgBox Cells(lRow, 6).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  ActiveSheet.Cells(lRow, 6).Select
End Sub

or shortly:

Sub check()
  For i = 1 to 1000000
    If Cells(i, 6).Value = "" Then
      Exit For
    End If
  Next

  MsgBox Cells(i, 6).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  ActiveSheet.Cells(i, 6).Select
End Sub

 

[Alex Blakenburg]

Here is an alternative approach.

Sub GetFirstEmptyRow()

    Dim ws As Worksheet
    Dim lo As ListObject
    Dim loColRng As Range
    Dim loRowNo As Long
    
    Set ws = Worksheets("RVO1")
    Set lo = Range("Table73").ListObject
    Set loColRng = Intersect(lo.DataBodyRange, Range("F12").EntireColumn)
    
    With Application
        loRowNo = .IfError(.Match("", loColRng, 0), 0)
    End With
    If loRowNo = 0 Then
        MsgBox "No empty cells found in Column of table"
        Exit Sub
    End If
    
    loColRng.Cells(loRowNo).Select

End Sub

 

[Ferryjaja]

Here is an alternative approach.

Sub GetFirstEmptyRow()

    Dim ws As Worksheet
    Dim lo As ListObject
    Dim loColRng As Range
    Dim loRowNo As Long
   
    Set ws = Worksheets("RVO1")
    Set lo = Range("Table73").ListObject
    Set loColRng = Intersect(lo.DataBodyRange, Range("F12").EntireColumn)
   
    With Application
        loRowNo = .IfError(.Match("", loColRng, 0), 0)
    End With
    If loRowNo = 0 Then
        MsgBox "No empty cells found in Column of table"
        Exit Sub
    End If
   
    loColRng.Cells(loRowNo).Select

End Sub

The alternative approach definitely works, but it says ''no empty cells found in column of table''. Probably because those ''empty'' cells have a formula in it that refers to another tab.

 

[Alex Blakenburg]

What are you actually looking for ?
Based on your comments the assumption is that the formula is returning "".
If this is not the case neither of the proposed solutions is going to find anything since they are both looking for "".
If your formula is something like

(='tab'!F16)

, then show us column tab!F to show us what type of data is in it.

 

[Ferryjaja]

Seems that I have been asking the wrong questions. The answer was right in front of me, I made an error in selecting the column that I wanted to search in. Now the cells dont have a function in it and therefore there is an easy solution.

Sorry for wasting your time