Set variable for loop in VBA based on # of rows with data

J88L

New Member
Joined
Nov 7, 2005
Messages
16
I'm creating a macro to add data to the end of a list. When imported, the data has three header rows. The first row of headers is found using:
Code:
Cells.Find(What:="Date:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
From there,
Code:
Activecell.Offset(4, 0).Select
...takes you to the first row of actual data. I now want the macro to capture how many rows of data (including that row) exist. Call it a pre-created variable named RowsWithData

How do I do that? :unsure:
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Code:
Public Sub Demo()


    Set FirstHdr = Cells.Find(What:="Date:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False)
    
    
    If FirstHdr Is Nothing Then
        MsgBox ("no Date header found ")
    Else
        FirstDataRow = FirstHdr.Row + 4
        DateColumn = FirstHdr.Column
        
        LastDataRow = Cells(65536, DateColumn).End(xlUp).Row
        
        ln1 = "Data Row " & FirstDataRow & vbNewLine
        ln2 = "Date Column " & DateColumn & vbNewLine
        ln3 = "Last Data Row " & LastDataRow & vbNewLine
        
        msg = ln1 & ln2 & ln3
        MsgBox (msg)
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,502
Members
412,671
Latest member
xcihan
Top