Solved: Last Row in Range (again)

[vzachin]

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]

Would i be correct in saying that was cell "i14" you want to identify/select?

[vzachin]

hi gerogiboy,

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

thanks
zach

[mdmackillop]

Here's a Function to keep it flexible

[vba]
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

[/vba]

[Kenneth Hobs]

[VBA]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[/VBA]

[georgiboy]

[VBA]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[/VBA]

Hope this helps

[mdmackillop]

Hi georgiboy,
That will only work with "solid" data, gaps will give misleading results
Regards
MD

[georgiboy]

Thanks for the heads up

[vzachin]

hi malcolm, kenneth & georgiboy,

thanks for the coding.

zach