PDA

View Full Version : Solved: Find the first Row



maninjapan
10-08-2008, 09:54 PM
I have the following code to find the last row

LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row

What would I change to get the first row?

mikerickson
10-08-2008, 10:21 PM
Dim FirstRow As Long
With ActiveSheet.Range("B:B")
With .Cells(1, 1)
FirstRow = IIf(.Value = "", .End(xlDown).Row, .Row)
End With
End With

david000
10-09-2008, 03:48 PM
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

Bob Phillips
10-09-2008, 03:51 PM
Not necessarily the first row, just the row before the first blank space above the last row.

MaximS
10-09-2008, 03:59 PM
you can also try this:


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

Paul_Hossler
10-09-2008, 06:25 PM
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
10-15-2008, 03:52 AM
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
10-15-2008, 05:11 AM
Sub FirstRow()

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

End Sub

maninjapan
10-15-2008, 06:07 AM
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
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

georgiboy
10-15-2008, 06:50 AM
Not sure what you are trying to achieve here but does this shed any light

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

Hope this helps

maninjapan
10-17-2008, 12:33 AM
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
10-17-2008, 12:37 AM
Just add this formula to K2 and copy down

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

maninjapan
10-17-2008, 12:42 AM
sorry XLD, Im not sure what this is supposed to do.....

Bob Phillips
10-17-2008, 12:46 AM
It calculates subtotal values by formula. Insert it and see.

georgiboy
10-17-2008, 07:35 AM
Works fine for me.

maninjapan
10-20-2008, 05:21 AM
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
10-20-2008, 05:48 AM
=IF(OR($B2="",COUNTIF($B2:$B$2000,$B2)>1),"",SUMIF($B:$B,$B2,$J:$J))

maninjapan
10-21-2008, 12:29 AM
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
10-22-2008, 06:08 AM
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)
' 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

Krishna Kumar
10-22-2008, 08:06 AM
Hi,

Try,

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

HTH

Demosthine
10-22-2008, 07:55 PM
Good Evening.

This is similar to the concept of Krishna, but uses Intersection rather than adjusting the several Ranges.



Public Sub Fill_Range()
' We want to have a specific Range we are referencing.
Dim rngSubRange As Range

' We set the SubRange to the set of Cells that intersect the UsedRange (A1:L9)
' with that of your Sub-Total Column (L:L). We adjust the SubRange slightly
' to avoid changing the Header Row.
Set rngSubRange = Intersect(UsedRange, Range("L2:L" & UsedRange.Rows.Count))
' We set the Formula for all of the cells in the Intersected Range (L2:L9) to
' the Formula that Krishna provided.
With rngSubRange
' Formula Equivalent for Cell L2:
' =IF(B2=B3,"",SUMIF(B:B,B2,L:L))
' This Formula first evaluates to see if the Value in the current Row is
' the same as the next Row. If it is, the Value is Blank. Otherwise,
' it needs to be the Sub-Total Value, using a slight variation of what
' XLD showed in Post #17. You can easily replace XLD's Post #17
' Formula with this one. They provide the same results.
.FormulaR1C1 = "=IF(RC[-10]=R[1]C[-10],"""",SUMIF(C[-10],RC[-10],C[-1]))"
' If you want to eliminate the Formula's once it's calculated, replace all
' of the Values in the Intersect Range with the results of the Formula.
' Comment this line if you want to keep the Formulas.
.Value = .Value
End With
End Sub


Scott

maninjapan
10-23-2008, 01:52 AM
Everyone, thanks. IT looks like Ive finally got it. Krishna, Id like to take this one step further and create a daily total. So this code works, just need to move it a column out each way. So it would group column A and total all the subtotals in column L in column N.

Demosthine
10-23-2008, 06:14 PM
Good Afternoon.

Adjusting the data to offset from where you are is easiest using the R1C1 Reference, which is part of the reason we used that in the Formula.

R1C1 Reference essentially uses a built in Offset method for it's conventions.

RC would refer to the current cell.

Now, the numbers in the brackets tell Excel which direction to move. A negative number moves left or up. A positive number moves right or down.

R[-1]C: Move 1 Row Up.
R[1]C: Move 1 Row Down.
R[1]C[1]: Move 1 Row Down and 1 Column Right

So if you wanted to place the formula in one cell to the left of where we referenced, you would have to move one less column over to get to the required data. Adjust your C[n] values as so:

"=IF(RC[-9]=R[1]C[-9],"""",SUMIF(C[-9],RC[-9],C))"

Make sense?
Scott

maninjapan
10-23-2008, 10:38 PM
Demosthine, yes its all clear now. Thankyou for taking the time to explain that. And thanks to everyone else who contributed to this thread.
This is what I was able to come up with. ( I also got rid of the first row)
Private Sub CommandDailyTotal_Click()

' Create the Daily Total

Dim DateAdd As String, TradSubAdd As String, f As String, NRow As Long

NRow = Range("A" & Rows.Count).End(xlUp).Row

DateAdd = Range("A1:A" & NRow).Address(ReferenceStyle:=xlR1C1)

TradSubAdd = Range("L1:L" & NRow).Address(ReferenceStyle:=xlR1C1)

f = "=if(rc[-13]<>r[1]c[-13],sumif(" & DateAdd & ",rc[-13]," & TradSubAdd & "),""zzz"")"

With Range("N2:N" & NRow)
.FormulaR1C1 = f
.Value = .Value
.Replace What:="zzz", Replacement:="", LookAt:=xlWhole
End With

End Sub

maninjapan
10-23-2008, 10:55 PM
One more thing. If I would like the total to appear 1 row below where it currently appears, where would I change?

GTO
10-23-2008, 11:07 PM
Are you talking about the values that end up in the range:

With Range("N2:N" & NRow)
.FormulaR1C1 = f
.Value = .Value
.Replace What:="zzz", Replacement:="", LookAt:=xlWhole
End With

Did you want to move the top and bottom of the range down one cell?

mark

maninjapan
10-26-2008, 11:00 PM
Mark, Id like the range to remain the same, just the row where the total is displayed to be moved down one.

Krishna Kumar
10-27-2008, 08:47 AM
Hi,

Private Sub CommandDailyTotal_Click()

' Create the Daily Total

Dim DateAdd As String, TradSubAdd As String, f As String, NRow As Long

NRow = Range("A" & Rows.Count).End(xlUp).Row

DateAdd = Range("A1:A" & NRow).Address(ReferenceStyle:=xlR1C1)

TradSubAdd = Range("L1:L" & NRow).Address(ReferenceStyle:=xlR1C1)

f = "=if(r[-1]c[-13]<>rc[-13],sumif(" & DateAdd & ",r[-1]c[-13]," & TradSubAdd & "),""zzz"")"

With Range("N3:N" & NRow + 1)
.FormulaR1C1 = f
.Value = .Value
.Replace What:="zzz", Replacement:="", LookAt:=xlWhole
End With

End Sub

maninjapan
10-28-2008, 12:39 AM
Thanks, that makes sense. I have one more though.
The following code cuts and pastes to the main spreadsheet. Currently it pastes it into the very next row. Where would I change it to paste 1 or 2 rows lower, leaving a gap?

Private Sub CommandButtonTransfer_Click()


' Copy Data over to main sheet

Dim sh1 As Worksheet
Dim LastRow As Long
Dim NextRow As Long





Set sh1 = Worksheets("Sheet1")
NextRow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row

With Sheets("Sheet3")
LastRow = GetLastRow
LastColumn = GetLastColumn
.Range("A1").Resize(LastRow, 16).Cut sh1.Cells(NextRow + 1, 1)
End With
End Sub

'-----------------------------------------------------------------
Function GetLastRow() As Long
'-----------------------------------------------------------------
GetLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function

Krishna Kumar
10-28-2008, 01:19 AM
Hi,


.Range("A1").Resize(LastRow, 16).Cut sh1.Cells(NextRow + 3, 1)

maninjapan
10-28-2008, 01:23 AM
so simple.... Thanks a lot.

maninjapan
10-29-2008, 03:40 AM
another question, slightly different, but still related to my project.
Id like to write a macro that finds the total of 3 figures. One being the last daily total. But Im not sure how to reference the most recent cell in column P containing a total.... attached is an example

GTO
10-29-2008, 04:09 AM
Is the subtotal always going to be five rows above the total?

maninjapan
10-29-2008, 04:25 AM
GTo, unfortunately not. It would be easy if it was. But as is in the spreadsheet I attached it will differ from day to day.

GTO
10-29-2008, 04:44 AM
Okay - by my poor recollection, there was inserting of rows, which was why you needed a formula (from xld I believe) that kept track of sub-totals. If this is accurate (again, my poor blonde memory may be off a wee bit), why couldn't you insert the rows consistently (to rows number) above where you want the total?

Maybe you could post a more updated example with the code/formulas in it thus far, so we could see what's going on? I really have to hit the sack, but will look tomorrow or Friday to see if answered.

Have a good day,

Mark

Krishna Kumar
10-29-2008, 05:10 AM
Hi,

In P19,

=SUM(LOOKUP(9.999999999E+307,$P$4:P18),M19:N19)

HTH

maninjapan
10-29-2008, 05:13 AM
Thanks Krishnar, This isnt just for a one off thing though, It will be added to every day, So I need a macro that will add todays Daily total and Extras to Yesterdays running total and then add the new total to colum P

maninjapan
10-29-2008, 05:46 AM
I dont know if this is the most efficient way to do this, but somehow I managed to come up with this and it works....
Sub Total_Daily()

Dim LastRowP As Long, LastRowMN As Long

' To find the last total in Column P

LastRowP = Range("P" & Rows.Count).End(xlUp).Row

' To find the last totals in Column M,N

LastRowMN = Range("M" & Rows.Count).End(xlUp).Row

' Adds the Previous Days running total + todays total + todays Extras

Cells(LastRowMN, 16) = Cells(LastRowP, 16) + Cells(LastRowMN, 13) + Cells(LastRowMN, 14)

End Sub

Krishna Kumar
10-29-2008, 09:59 AM
Hi,

Sub SubTotals()
Dim r As Range, a As Range, SumRng As String

Set r = Range("a5", Range("a" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each a In r.Areas
SumRng = a.Offset(-1, 15).Address(, , xlR1C1)
a.Cells(1, 1).Offset(a.Rows.Count, 15).FormulaR1C1 = "=sum(" & SumRng & ",rc[-3]:rc[-2])"
Next
End Sub

HTH