Detect the last entry in a Column to search for a value within the used range

Question

I am trying to check if a value is in a certain range. If that value appears, the corresponding data to that enty is copied to another sheet. The trick is it has to be dynamically determined because the column size varies from input sheet to input sheet. In Java the hasNext() function can be used. I think that VBA's most similar function would be Sheet1.Column.End(xlup). How is the best way to determine the end of a column for the test condition of a do while loop in vba?

Pseudo example:

'This is part of a nested loop, this segment traverses the column looking for 'specified data.

Do While (!Sheets(inputSheetName).SyntaxToDetermineEndOfColumn))
     If(someCell = someValue)
          Copy values from the corresponding row to fields in newSheet
     End If
     Next r        'This increments the row within the current column
Loop
Next c             'This increments to the next column of data

Answer 1

There are two parts to your question:

The first part about finding the last used row is easialy found with a quick Google: Error in finding last used cell in VBA

To loop from start to end of column, use this:

Dim ws1 as Worksheet, LastRow as Long, CurRow as Long, DataFind as String

Set ws1 = Sheets("Name of Sheet")
LastRow = ws1.Range("Column letter" & ws1.Rows.Count).End(xlUp).Row

DataFind = Inputbox("What are you looking for?")

For CurRow = 1 to LastRow
    If ws1.Range("Column Letter" & CurRow).Value = DataFind Then
        ws1.Range("Column Letter" & CurRow).EntireRow.Copy
        Sheets("Dest Sheet").Range("Whatever").PasteSpecial
    End If
Next CurRow

Answer 2

Say we have data like:

We want to find happiness in the first two columns and retrieve the column C value in that row:

Sub LookingForHappiness()
    Dim i As Long, j As Long, N As Long, h As String
    h = "happiness"
    For i = 1 To 2
        N = Cells(Rows.Count, i).End(xlUp).Row
        For j = 1 To N
            If Cells(j, i).Value = h Then
                MsgBox Cells(j, "C").Value
                MsgBox Cells(j, i).Address(0, 0)
                Exit Sub
            End If
        Next j
    Next i
End Sub

Answer 3

You might find this useful: http://support.microsoft.com/kb/830287

But what I personally do in this situation involves a bit more code, but is flexible and fast. First create a class and call it "RangeInfo". Then past this:

Option Explicit

Private Type Properties
    Intialized As Boolean
    Object As Excel.Range
    RowBottom As Long
    RowCount As Long
    RowTop As Long
    ColumnLeft As Long
    ColumnCount As Long
    ColumnRight As Long
End Type

Private this As Properties

Public Property Get Initialized() As Boolean
    Initialized = this.Intialized
End Property

Public Property Get Object() As Excel.Range
    Set Object = this.Object
End Property

Public Property Get ColumnLeft() As Long
    ColumnLeft = this.ColumnLeft
End Property

Public Property Get ColumnCount() As Long
    ColumnCount = this.ColumnCount
End Property

Public Property Get ColumnRight() As Long
    ColumnRight = this.ColumnRight
End Property

Public Property Get RowBottom() As Long
    RowBottom = this.RowBottom
End Property

Public Property Get RowCount() As Long
    RowCount = this.RowCount
End Property

Public Property Get RowTop() As Long
    RowTop = this.RowTop
End Property

Public Sub Initialize(ByRef rng As Excel.Range)
    With this
        Set .Object = rng
        .RowTop = rng.row
        .RowCount = rng.Rows.Count
        .RowBottom = .RowTop + .RowCount - 1&
        .ColumnLeft = rng.Column
        .ColumnCount = rng.Columns.Count
        .ColumnRight = .ColumnLeft + this.ColumnCount - 1&
        .Intialized = True
    End With
End Sub

Public Sub Clear()
    Dim emptyProperties As Properties
    this = emptyProperties
End Sub

Private Sub Class_Terminate()
    Set this.Object = Nothing
End Sub

Then for your code, use this:

Option Explicit

Public Sub Example()
    'Set these as needed:
    Const sheetName As String = "MySheet"
    Const columnNumber As Long = 2&
    Const criteria As String = "*foo#"

    Dim wsIn As Excel.Worksheet
    Dim wbOut As Excel.Workbook
    Dim wsOut As Excel.Worksheet
    Dim ri As RangeInfo
    Dim rowIn As Long
    Dim rowOut As Long
    Dim col As Long


    Set wbOut = Excel.Workbooks.Add
    Set wsOut = wbOut.Worksheets(1)
    Set wsIn = Excel.Worksheets(sheetName)
    Set ri = New RangeInfo
    ri.Initialize wsIn.UsedRange
    rowOut = 1&
    With ri
        For rowIn = .RowTop To .RowBottom
            If wsIn.Cells(rowIn, columnNumber) Like criteria Then
                rowOut = rowOut + 1&
                For col = .ColumnLeft To .ColumnRight
                    wsOut.Cells(rowOut, col).Value = wsIn.Cells(rowIn, col).Value
                Next
            End If
        Next
    End With

End Sub