CODEWITHSUNDEEP
excelvbarowcount
jacob_m
jacob_m

Reputation: 31

Row Counter Only Counting? Top Row

My code is supposed to select all of the items in A-H from the top of the sheet to the bottom most row containing text in the J column. However, now all it does is select the top row. This code has worked fine elsewhere for other purposes, but when I run it here it only selects the top row.

Here is the code and what it currently does. The commented out bit does the same when it is ran in the place of the other finalrow =statement.

Option Explicit

Sub FindRow()

Dim reportsheet As Worksheet
Dim finalrow As Integer

Set reportsheet = Sheet29

Sheet29.Activate
'finalrow = Cells(Rows.Count, 10).End(xlUp).Row
finalrow = Range("J1048576").End(xlUp).Row


    If Not IsEmpty(Sheet29.Range("B2").Value) Then
         Range(Cells(1, 1), Cells(finalrow, 8)).Select

    End If
End Sub

enter image description here

This is the excerpt of code with a row counter that works.

datasheet.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row

''loop through the rows to find the matching records
For i = 1 To finalrow
    If Cells(i, 1) = item_code Then ''if the name in H1 matches the search name then
        Range(Cells(i, 1), Cells(i, 9)).Copy ''copy columns 1 to 9 (A to I)
        reportsheet.Select ''go to the report sheet
        Range("A200").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False ''find the first blank and paste info there
        datasheet.Select ''go back to the data sheet and continue searching
        End If
Next i

Upvotes: 0

Views: 99

Answers (1)

Kubie
Kubie

Reputation: 1571

You can try this:

Option Explicit
Sub FindRow()

    ' always use Longs over Integers
    Dim finalrow As Long: finalrow = 1

    ' you might not need this line tbh
    Sheet29.Activate

    With Sheet29

        ' custom find last row
        Do While True
            finalrow = finalrow + 1
            If Len(CStr(.Range("J" & finalrow).Value)) = 0 Then Exit Do
        Loop

        ' Len() is sometimes better then IsEmpty()
        If Len(CStr(.Range("B2").Value)) > 0 Then
            .Range(.Cells(1, 1), .Cells((finalrow - 1), 8)).Select
        End If
    End With

End Sub

Upvotes: 1

Related Questions