First cell with Data in a Variable

[HGDantes]

Hello

In the following code, I would like to put the Row number of the first line with Data in a variable. (or be able to start my selection from the first line with Data in column A) Right now, my script works well, only when the first line with Data, is Line 2.

ActiveSheet.Range("A:A").AutoFilter Field:=5, Criteria1:="JD"
If IsEmpty(Range("A2").Value) = False Then
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End If

Thanks

 

[JoeMo]

Hello

In the following code, I would like to put the Row number of the first line with Data in a variable. (or be able to start my selection from the first line with Data in column A) Right now, my script works well, only when the first line with Data, is Line 2.

ActiveSheet.Range("A:A").AutoFilter Field:=5, Criteria1:="JD"
If IsEmpty(Range("A2").Value) = False Then
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End If

Thanks

Does your data in col A have a header? If yes, what is the header?

 

[HGDantes]

Does your data in col A have a header? If yes, what is the header?

Yes it does. Header is "Date"

 

[JoeMo]

This will return the row number of the first cell below the header cell in column A, provided the header cell is visible.

Sub FirstDataCell()
'returns the row number of the cell after a header cell in column A, provided the header cell is visible.
Dim FirstDataCell As Range
Const Hdr As String = "Date"   'change to suit
On Error Resume Next
Set FirstDataCell = Range("A:A").Find(what:=Hdr, after:=Cells(Rows.Count, "A").End(xlUp), searchdirection:=xlNext, LookIn:=xlValues, lookat:=xlWhole).Offset(1, 0)
On Error GoTo 0
If Not FirstDataCell Is Nothing Then
    MsgBox "Row " & FirstDataCell.Row
Else
    MsgBox "A cell with the header " & Hdr & " in it is not visible in column A"
End If
End Sub

 

[HGDantes]

Thanks, but it still not working properly. Here it's returning Row 2. In this example, I would like it to return Row 8.

Thanks

Regards

 

[JoeMo]

OK, try this.
This will return the row number of the first visible cell below the header cell in column A, provided the header cell is visible.

Sub FirstDataCell()
'returns the row number of the first visible cell after a header cell in column A
Dim HdrCell As Range, FirstDataCell As Range
Const Hdr As String = "Date"   'change to suit
On Error Resume Next
Set HdrCell = Range("A:A").Find(what:=Hdr, after:=Cells(Rows.Count, "A").End(xlUp), searchdirection:=xlNext, LookIn:=xlValues, lookat:=xlWhole)
On Error GoTo 0
If Not HdrCell Is Nothing Then
    On Error Resume Next
    Set FirstDataCell = Range(HdrCell.Offset(1, 0), Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)(1)
    On Error GoTo 0
    If Not FirstDataCell Is Nothing Then
        If FirstDataCell.Address = HdrCell.Address Then
            MsgBox "There are no visible cells in the data range below the header cell."
        Else
            MsgBox "First visible data cell is in Row " & FirstDataCell.Row
        End If
    End If
Else
    MsgBox "A cell with the header " & Hdr & " in it is not visible in column A"
End If
End Sub

 

[HGDantes]

OK, try this.
This will return the row number of the first visible cell below the header cell in column A, provided the header cell is visible.

Sub FirstDataCell()
'returns the row number of the first visible cell after a header cell in column A
Dim HdrCell As Range, FirstDataCell As Range
Const Hdr As String = "Date"   'change to suit
On Error Resume Next
Set HdrCell = Range("A:A").Find(what:=Hdr, after:=Cells(Rows.Count, "A").End(xlUp), searchdirection:=xlNext, LookIn:=xlValues, lookat:=xlWhole)
On Error GoTo 0
If Not HdrCell Is Nothing Then
    On Error Resume Next
    Set FirstDataCell = Range(HdrCell.Offset(1, 0), Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)(1)
    On Error GoTo 0
    If Not FirstDataCell Is Nothing Then
        If FirstDataCell.Address = HdrCell.Address Then
            MsgBox "There are no visible cells in the data range below the header cell."
        Else
            MsgBox "First visible data cell is in Row " & FirstDataCell.Row
        End If
    End If
Else
    MsgBox "A cell with the header " & Hdr & " in it is not visible in column A"
End If
End Sub

Super, It's working Perfectly now.

Thanks a lot

 

[JoeMo]

Super, It's working Perfectly now.

Thanks a lot

You are welcome - thanks for the reply.