Re: Find last row with data in set range

On 5 Dec, 11:06, Jay <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi Neil -

Sub LastRow()
Dim a As Integer

For a = 12 To 100
If Range("A" & a) = "" Or _
Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
End If
Next a

MsgBox "Last Row = " & LastR
End Sub

----
Jay



"neil_...@xxxxxxxxxxxxx" wrote:
Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks- Hide quoted text -

- Show quoted text -

Hi Jay,

Thanks for the response it works perfectly but it looks as though it
does not work with the rest of the coding I have and trips up and
gives me an error message 9 subscript out of range when it goes
through my array code - any ideas how I can make this code work??

Dim TaskData() As Variant

Call LastRow

TaskData() = Sheets("New Time Sheet").Range("A12:L" & LastR).Value

'TaskData() = Sheets("New Time Sheet").Range("A12:L12").Value

Dim Tempdata() As Variant

c = 0
For a = 1 To (LastR - 11) Step 1
'For a = 1 To 21 Step 1
If TaskData(a, 11) = "" Then

Else
c = c + 1
ReDim Preserve Tempdata(12, c)
For d = 1 To 12 Step 1
Tempdata(d, c) = TaskData(a, d)
Next d
End If
Next a

'-----invert array-----

ReDim TaskData(c, 12)
For a = 1 To c Step 1
For b = 1 To 12 Step 1
TaskData(a, b) = Tempdata(b, a)
Next b
Next a

Thanks Val
.

Loading