Locate first empty cell in range of columns

[Ironman]

Hi

Sheet 'Daily Tracking' contain years from 1984-2061 in Cols D1:CC1
Rows 2:367 contain days of the year (incl Feb 29)

I'm looking for some code that selects the first cell in the column (searching from the top of the sheet downwards) in the current year (whichever year that is) that does not contain a value or a zero value i.e. blank.

Many thanks!

 

[Ironman]

PS - Row 61 is Feb 29 and if it is not a leap year this cell will be blank (no zero value). It therefore needs to be excluded from the selection.

 

[DanteAmor]

Try this:

Sub firstblank()
  Dim f As Range
  'If it is the last data of the last row used, for this code, this trick does not send an error when selecting a new row.
  With Range("D" & ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row + 1)
    .Value = " "
    .Value = ""
  End With
  Set f = Range("D1", Cells(1, Columns.Count).End(1)).Find(Year(Date), , xlValues)
  If Not f Is Nothing Then
    Range(Cells(2, f.Column), Cells(Rows.Count, f.Column)).SpecialCells(4)(1).Select
  End If
End Sub

 

[Ironman]

Hi Dante, good to hear from you and thanks for replying!

I got a 1004 error with this line:

Range(Cells(2, f.Column), Cells(Rows.Count, f.Column)).SpecialCells(4)(1).Select

 

[DanteAmor]

PS - Row 61 is Feb 29 and if it is not a leap year this cell will be blank (no zero value). It therefore needs to be excluded from the selection.

Try this code, it includes the adjustment for the leap year.

Sub firstblank_2()
  Dim f As Range
  Dim i As Long
  
  Set f = Range("D1", Cells(1, Columns.Count).End(1)).Find(Year(Date), , xlValues)
  If Not f Is Nothing Then
      For i = 2 To Rows.Count
        If Cells(i, f.Column).Value = "" Then
          If i = 61 Then
            If Day(DateSerial(Year(Date), 3, 1) - 1) = 29 Then
              Cells(i, f.Column).Select
              Exit Sub
            End If
          Else
            Cells(i, f.Column).Select
            Exit Sub
          End If
        End If
      Next
  End If
End Sub

 

[Ironman]

Aw, beautiful, that works brilliantly!

Thank you so much once again Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.