Search Till the last row in Excel VBA

Question

i am running a macro and i want to search till the last row with data. Currently i have defined the last row as 1555. This is not really efficient in case there are more than 1555 rows . Which is the best way to approach this ? Option Explicit

Sub FindValues()
Dim LSearchRow As Integer
Dim rw As Integer, cl As Range, LSearchValue As Long, LCopyToRow As Integer
Dim LSearchString As String
Dim iHowMany     As Integer
Dim aSearch(15)  As Long
Dim i  As Integer


'On Error GoTo Err_Execute
Sheet2.Cells.Clear
Sheet1.Select
 iHowMany = 0
 LSearchValue = 99

'this for the end user to input the required A/C to be searched

Do While True

    LSearchString = InputBox( _
           "Please enter a value to search for. " & _
           "Enter a zero to indicate finished entry", _
           "Enter Search value")

    If IsNumeric(LSearchString) Then

        LSearchValue = CLng(LSearchString)
        If LSearchValue = 0 Then Exit Do
        iHowMany = iHowMany + 1

        If iHowMany > 15 Then

            MsgBox "You are limited to 15 search numbers.", vbOKOnly, "Limit reached"
            iHowMany = 15
            Exit Do

        End If

        aSearch(iHowMany) = LSearchValue

    End If

Loop

If iHowMany = 0 Then

    MsgBox "No selections entered.", vbOKOnly + vbCritical, "No Search data"

    Exit Sub

End If

LCopyToRow = 2

For rw = 1 To 1555
    For Each cl In Range("A" & rw & ":N" & rw)
    '------------------------------------------------
        For i = 1 To iHowMany
            Debug.Print cl.Row & vbTab & cl.Column
            LSearchValue = aSearch(i)
            If cl = LSearchValue Then
                cl.EntireRow.Copy

                'Destination:=Worksheets("Sheet2")
                '.Rows(LCopyToRow & ":" & LCopyToRow)

                Sheets("Sheet2").Select
                Rows(LCopyToRow & ":" & LCopyToRow).Select

                'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                   xlNone, SkipBlanks:=False, Transpose:=False

                'Move counter to next row
                LCopyToRow = LCopyToRow + 1

                'Go back to Sheet1 to continue searching
                Sheets("Sheet1").Select
            End If
        Next i
        'LSearchRow = LSearchRow + 1
    Next cl
Next rw

'Position on cell A3
'Application.CutCopyMode = False
'Selection.Copy

Sheets("Sheet2").Select
Cells.Select

'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    'SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
Sheet2.Select


MsgBox "All matching data has been copied."

Exit Sub

'Err_Execute:
MsgBox "An error occurred: " & Err.Number & vbTab & Err.Description
Exit Sub
Resume Next
End Sub

I am trying to work with this : Dim DataRange As Range , Dim lastrow as long Set DataRange = Range("A1:N" & LastRow). Not sure how to define lastrow — kay, Nov 23 '14 at 11:42

score 1 · Answer 1 · answered Nov 23 '14 at 10:48

1

You could use Do Until. I have attached an example below which takes all rows from column A.

'Select cell A1, *first line of data*.
Range("A1").Select

'Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
    ' Do something here

    ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
Loop

answered Nov 23 '14 at 10:48

Alex Szabo

3,274
2
18
30

HI thanks for that .I have been trying to run that but its affecting other parts of my code. – kay Nov 23 '14 at 11:30
I am trying to work with this : Dim DataRange As Range , Dim lastrow as long Set DataRange = Range("A1:N" & LastRow). Not sure how to define lastrow – kay Nov 23 '14 at 11:41
1

Unreliable way! What if the last row which has data is `300` and you have a blank cell in say `A100` Your do loop will exit at `100`. – Siddharth Rout Nov 23 '14 at 12:07

score 0 · Answer 2 · edited Nov 23 '14 at 16:20

0

Dim LastRow As Long
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

edited Nov 23 '14 at 16:20

Automate This

30,726
11
60
82

answered Nov 23 '14 at 12:00

kay

29
2
13

Search Till the last row in Excel VBA

2 Answers2