Solved: Find the first Row

[maninjapan]

I have the following code to find the last row

[VBA] LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row[/VBA]

What would I change to get the first row?

[mikerickson]

[VBA]Dim FirstRow As Long
With ActiveSheet.Range("B:B")
With .Cells(1, 1)
FirstRow = IIf(.Value = "", .End(xlDown).Row, .Row)
End With
End With[/VBA]

[david000]

[VBA]
Sub Test()
Dim LastRow As Range
Dim FirstRow As Long

Set LastRow = Range("d" & Rows.Count).End(xlUp)
FirstRow = LastRow.End(xlUp).Row

Range("D" & FirstRow).Select
End Sub

[/VBA]

[Bob Phillips]

Not necessarily the first row, just the row before the first blank space above the last row.

[MaximS]

you can also try this:

[VBA]
Dim FirstRow As Long
FirstRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
[/VBA]

[Paul_Hossler]

How do you want to define "First Row"?

For example, if there is Data in

1. A1:A10, first row is A1
2. A4:A10, first row is A4 (correct?)
3. A4:A10, and A20:A30 first row is A4 (correct?)

Paul

[maninjapan]

Once again I didnt explain myself very well. Paul. Choice 3 would best describe 'First row'. The First new row would start from the first empty cell after a subtotal in column L.

[georgiboy]

[VBA]Sub FirstRow()

For Each r In Range("A:A").Cells
If r.Value <> "" Then
r.Select
Exit Sub
End If
Next

End Sub[/VBA]

[maninjapan]

Sorry Georgiboy, Im not sure how to fit that in to the following code that I currently use.
Id like to change the I1 part, to I " Row First empty cell after previous subtotal in Column I"
I
[VBA]Private Sub CommandTraderTotal_Click()

' Subtotal Trader PL

Dim I_LastRow As Integer

With Sheet3

I_LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row

.Cells(I_LastRow + 0, "L").Formula = "=SUM(I1:" & I_LastRow & ")"
End With

End Sub[/VBA]

[georgiboy]

Not sure what you are trying to achieve here but does this shed any light

[VBA]Private Sub CommandTraderTotal_Click()

' Subtotal Trader PL

Dim I_LastRow As Integer

For Each r In Sheet3.Range("I:I").Cells
If r.Value <> "" Then
I_LastRow = r.Row
GoTo jump1
End If
Next

jump1:

Sheet3.Range("L" & I_LastRow).Value = WorksheetFunction.Sum(Range("i1:i" & I_LastRow))

End Sub[/VBA]

Hope this helps

[maninjapan]

Ive made up a quick example of what Im trying to achieve.
I need the range to be totaled to only start from after the previous subtotal, not from I1 every time as the code below does.
"=SUM(I1:" & I_LastRow & ")"

[Bob Phillips]

Just add this formula to K2 and copy down

=IF(COUNTIF($B2:$B$2000,$B2)>1,"",SUMIF($B:$B,$B2,$J:$J))

[maninjapan]

sorry XLD, Im not sure what this is supposed to do.....

[Bob Phillips]

It calculates subtotal values by formula. Insert it and see.

[georgiboy]

Works fine for me.

[maninjapan]

Ok, I figured it out. It does what I need it to do, thanks for that, but the problem with using this method is it leaves zeros in columns yet to be filled. I use a macro to cut and paste the days subtotals to a different spreadsheet, (which is why I needed to find the last row) taking all the extra zeros with it as well as leaving column K without the formulas. This would mean I have to add the formula to column K each day before I add all the totals.....

[Bob Phillips]

=IF(OR($B2="",COUNTIF($B2:$B$2000,$B2)>1),"",SUMIF($B:$B,$B2,$J:$J))

[maninjapan]

Thanks XLD, that works better, but I still have the problem of having to add the formula to colum K after I use a macro to cut and paste it to another spreadsheet , is there anyway to imbed the formula into column K so its permanent?

[maninjapan]

Actually Ive tried this and it almost works. The Problem is, its using the row with the previous subtotal, not the row below it. (I know that the code is referring to the last row of the previous subtotal, I just dont know how to make it 'Last row of previous subtotal + 1 row)
[VBA]' Subtotal Trader PL


Dim J_LastRow As Integer
Dim J_FirstRow As Integer

With Sheet3

' Find the last row to be totaled

J_LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row

' Find the first row after the previous Subtotal

J_FirstRow = .Cells(.Rows.Count, "L").End(xlUp).Row

.Cells(J_LastRow + 0, "L").Formula = "=SUM(J" & J_FirstRow & ":J" & J_LastRow & ")"
End With

End Sub[/VBA]

[Krishna Kumar]

Hi,

Try,

[vba]Sub SubTotalTrader()
Dim TradAdd As String, NetAdd As String, f As String, lRow As Long
lRow = Range("b" & Rows.Count).End(xlUp).Row
TradAdd = Range("b2:b" & lRow).Address(ReferenceStyle:=xlR1C1)
NetAdd = Range("j2:j" & lRow).Address(ReferenceStyle:=xlR1C1)
f = "=if(rc[-10]<>r[1]c[-10],sumif(" & TradAdd & ",rc[-10]," & NetAdd & "),""zzz"")"
With Range("l2:l" & lRow)
.FormulaR1C1 = f
.Value = .Value
.Replace What:="zzz", Replacement:="", LookAt:=xlWhole
End With
End Sub[/vba]

HTH