Re: Find last row with data in set range

On 5 Dec, 13:13, Jay <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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



"neil_...@xxxxxxxxxxxxx" wrote:
On 5 Dec, 12:13, Jay <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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_...@xxxxxxxxxxxxx" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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
.