Find last used row in named range with data all around
LooneyTunes used application.counta() to count the number of rows used.
If your data is nice and contiguous (no gaps), you'll be fine. But as soon as
the data gets gaps, all bets are off.
venus wrote:
Hi Looney,
Well this code works better, but instead of the last row, it gives me
2-rows before the last. I'm not sure why...
Venus
LooneyTunes wrote:
Sorry, my bad... here's another snippet to try
Sub FindLastRowInMyRange()
Dim rng As Range
Dim i As Integer, j As Integer, k As Integer
Dim iLastRow As Integer, iLastCol As Integer
Dim sLastAddress As String
'skinny version:
k = 0
Set rng = Range("hours")
For i = 1 To rng.Columns.Count
j = Application.CountA(rng.Columns(i))
If j k Then
iLastRow = rng.Rows(j).Row
k = j
End If
Next i
MsgBox "The last used row in the range is: row " & iLastRow
'=========== If you want more info
k = 0
Set rng = Range("hours")
For i = 1 To rng.Columns.Count
j = Application.CountA(rng.Columns(i))
If j k Then
iLastRow = rng.Rows(j).Row
iLastCol = rng.Columns(i).Column
k = j
sAddress = Cells(iLastRow, iLastCol).Address
End If
Next i
MsgBox "The last used row in the range is: row " & iLastRow _
& vbLf & "The last used column is: Column(" & iLastCol & ") or (" &
Columns(iLastCol).Address & ")" _
& vbLf & "The last cell address is: " & sAddress
End Sub
Sorry for the boo boo
Good luck and happy programming
LooneyTunes
venus wrote:
Thank you all so very much!
All codes worked but for Lonney Tunes, it only gave me the last row of
the range as Dave suggested.
God bless,
Venus
Dave Peterson wrote:
Won't that just return the last row of the range--whether or not it's used?
LooneyTunes wrote:
Try this little snippet of code:
Sub FindLastRowInMyRange()
Dim rng As Range
Dim i As Integer
'to find the last used row in your named range _
regardless of where it is
Set rng = Range("hours")
i = rng.Rows(rng.Rows.Count).Row
'proof
MsgBox "The last used row within your named range is row: " & i
'to find the next row after your named range
i = i + 1
'proof
MsgBox "Start your next row he row " & i
End Sub
Good luck and happy programming
LooneyTunes
--
Dave Peterson
|