Find last row with data in set range

[neil_val]

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

 

[Jay]

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

 

[neil_val]

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














- 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

 

[Jay]

Hi Val -

Your code seems to run fine; I can't reproduce the error. On what line does
the error occur and what are the values of a, b, c, and d at the time?

Jay

 

[neil_val]

Hi Val -

Your code seems to run fine; I can't reproduce the error. On what line does
the error occur and what are the values of a, b, c, and d at the time?

Jay

















- Show quoted text -

Jay,

Many thanks for replying it seems that the Array problem is fine after
I stepped through the entire Project. The problem that I am
encountering is when there is no blank rows between "Total" and the
data and this is where it is tripping up:

a should be = 25
b should be = 24

But it is saying that b is = 25

here is the rest of the code

Dim b As Integer

For a = 12 To 100
If Range("A" & a) = "" Then
b = a - 1
Exit For
Else
End If
Next a

'Stop

If b = 12 Then b = 13

For a = 1 To (b - 11) Step 1

'For a = 1 To (b - 11) Step 1

rs.addnew
rs("WKCOMDATE") = week
rs("PROJECTCODE") = HoldingTableData(a, 1)
rs("WORKCODE") = HoldingTableData(a, 2)
rs("MON") = HoldingTableData(a, 3)
rs("TUE") = HoldingTableData(a, 4)
rs("WED") = HoldingTableData(a, 5)
rs("THU") = HoldingTableData(a, 6)
rs("FRI") = HoldingTableData(a, 7)
rs("SAT") = HoldingTableData(a, 8)
rs("SUN") = HoldingTableData(a, 9)
rs("TOTALHRS") = HoldingTableData(a, 10)
rs("TASKCATEGORY") = HoldingTableData(a, 11)
rs("PARTNUMBER") = HoldingTableData(a, 12)
'''''rs("REPORTINGMONTH") = MonthName(Month(Date))
rs("EMPLOYEESNAME") = who
rs("DATESUBMITTED") = Date
'rs("DEPARTMENT") = dept
'rs("DATESUB") = Now()
Next a
rs.update

rs.Close
cnn1.Close
Set cnn1 = Nothing
Set rs = Nothing

'MsgBox (msg)

End Sub

 

[Jay]

Val -

Try changing the line:
If Range ("A" & a) = "" Then
To:
If Range("A" & a) = "" Or Left(Range("A" & a), 5) = "Total" Then

That should generate the correct value for 'b' when there are no blank data
rows.

 

[neil_val]

Val -

Try changing the line:
If Range ("A" & a) = "" Then
To:
If Range("A" & a) = "" Or Left(Range("A" & a), 5) = "Total" Then

That should generate the correct value for 'b' when there are no blank data
rows.

---
Jay
















- Show quoted text -

Jay,

I am afraid I already tried that and still no joy?? and the Sub
LastRow code seems to have had an impact on my insert blank row
cmdbutton??? What a can of worms.


Thanks

 

[Jay]

Hmmm.. sounds like a few issues are brewing in the background. It's up to
you at this point, but you're welcome to re-evaluate the issues and re-post
with your latest and most complete code along with any descriptions we might
need (buttons, etc.).

If the 'worm can' is difficult to describe, feel free to email it to me at
jc_._sresearch_@_hotmail_._com (remove the underscores "_") along with the
latest errors (and where they occur) and I'll take a look.

I have to sign off for 8-10 hrs, but I'll pick it up after that if you
decide to repost.

Thanks for the opportunity and sorry we couldn't nail it down in short order.

 

[neil_val]

Val -

Try changing the line:
If Range ("A" & a) = "" Then
To:
If Range("A" & a) = "" Or Left(Range("A" & a), 5) = "Total" Then

That should generate the correct value for 'b' when there are no blank data
rows.

---
Jay
















- Show quoted text -

Hi Jay,

Thanks for that already tried that with no joy? I have also noticed
that my InstertRow cmdbutton is now not functioning as it used to
since the change of the Sub LastRow() ???

 

[neil_val]

Hi Jay,

Thanks for that already tried that with no joy? I have also noticed
that my InstertRow cmdbutton is now not functioning as it used to
since the change of the Sub LastRow() ???- Hide quoted text -

- Show quoted text -

Jay

Thanks for that will re-post and again thanks for all your help

Val