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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.