PDA

View Full Version : Solved: Last Row in Range (again)



vzachin
10-16-2008, 08:55 AM
hi,

this recent thread http://www.vbaexpress.com/forum/showthread.php?t=22716 didn't help me with my question.

i have data in multiple columns. i need to find the last row with data within a range of columns (column h to column m). i need to write data to the next blank row. the rows are not hidden & does not contain formulas.

i have looked at other solutions but i couldn't find one that dealt with specific ranges.


thanks
zach

georgiboy
10-16-2008, 09:00 AM
Would i be correct in saying that was cell "i14" you want to identify/select?

vzachin
10-16-2008, 09:12 AM
hi gerogiboy,

i would need to know row 14, not necessary "i14"

thanks
zach

mdmackillop
10-16-2008, 09:29 AM
Here's a Function to keep it flexible


Option Explicit
Sub LastRw()
MsgBox LastRow("H", "M")
End Sub

Function LastRow(Strt As Variant, Endd As Variant) As Long
Dim Rw As Long, R As Long, i As Long

Strt = Columns(Strt).Column
Endd = Columns(Endd).Column
Rw = 0
For i = Strt To Endd
R = Cells(Rows.Count, i).End(xlUp).Row
If R > Rw Then Rw = R
Next
LastRow = Rw
End Function

Kenneth Hobs
10-16-2008, 09:38 AM
Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Range("H1:M" & Rows.Count).Find(What:="*", After:=[H1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow+1
End If
End Sub

georgiboy
10-16-2008, 09:44 AM
Sub Last()
Dim h, i, j, k, l, m As Integer

h = WorksheetFunction.CountA(Range("H:H"))
i = WorksheetFunction.CountA(Range("i:i"))
j = WorksheetFunction.CountA(Range("j:j"))
k = WorksheetFunction.CountA(Range("k:k"))
l = WorksheetFunction.CountA(Range("l:l"))
m = WorksheetFunction.CountA(Range("m:m"))

'finds the column with the most data, then adds the ammount of empty rows above your data
MsgBox WorksheetFunction.Max(h, i, j, k, l, m) + 9 '+ 8 is the ammount of empty rows above your data

End Sub

Hope this helps

mdmackillop
10-16-2008, 09:53 AM
Hi georgiboy,
That will only work with "solid" data, gaps will give misleading results
Regards
MD

georgiboy
10-16-2008, 09:59 AM
Thanks for the heads up :doh:

vzachin
10-16-2008, 11:08 AM
hi malcolm, kenneth & georgiboy,

thanks for the coding.

zach