Find last used row in named range with data all around
 

Hi,

I'm fairly good with Excel, but new to vba (I barely know what I'm
doing). I have a named range of cells c6:p53, named Hours. There is
data all around this range. I want to find the last used row within
this range and have had no luck.

Almost every solution I've found just gets me the very last row of the
worksheet instead of the range. There is a thread with a solution from
Jim Thomlinson, but I get an "invalid qualifier" error when I use it. I
have looked for days on the net and when I find code that should work,
it doesn't. What's wrong with me and my pc?

Someone, please help me!

Find last used row in named range with data all around
 

Sub CallTheBottomRowFunction()
Dim lngRow As Long
lngRow = GetBottomRow(ActiveSheet.Range("Hours"))
MsgBox lngRow & " is the last row. "
End Sub

Function GetBottomRow(ByRef TheRange As Excel.Range) As Long
On Error GoTo NoRow
GetBottomRow = TheRange.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
----------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"venus"

wrote in message
Hi,
I'm fairly good with Excel, but new to vba (I barely know what I'm
doing). I have a named range of cells c6:p53, named Hours. There is
data all around this range. I want to find the last used row within
this range and have had no luck.

Almost every solution I've found just gets me the very last row of the
worksheet instead of the range. There is a thread with a solution from
Jim Thomlinson, but I get an "invalid qualifier" error when I use it. I
have looked for days on the net and when I find code that should work,
it doesn't. What's wrong with me and my pc?
Someone, please help me!

Find last used row in named range with data all around
 

This is specific to your range:

Sub venus()
For i = 53 To 3 Step -1
If Application.CountA(Range(Cells(i, "C"), Cells(i, "P"))) 0 Then
Exit For
End If
Next
MsgBox ("Row " & i & " has data")
End Sub

You don't need to use the name of the range.

--
Gary's Student


"venus" wrote:

Hi,

I'm fairly good with Excel, but new to vba (I barely know what I'm
doing). I have a named range of cells c6:p53, named Hours. There is
data all around this range. I want to find the last used row within
this range and have had no luck.

Almost every solution I've found just gets me the very last row of the
worksheet instead of the range. There is a thread with a solution from
Jim Thomlinson, but I get an "invalid qualifier" error when I use it. I
have looked for days on the net and when I find code that should work,
it doesn't. What's wrong with me and my pc?

Someone, please help me!

Find last used row in named range with data all around
 

I'd just loop through the rows--starting at the bottom.

Option Explicit
Sub testme01()

Dim iRow As Long
Dim myRng As Range
Dim LastRow As Long

With Worksheets("Sheet1")
Set myRng = .Range("Hours") 'c6:p53
End With

LastRow = 0
With myRng
For iRow = .Rows.Count To 1 Step -1
If Application.CountA(myRng.Rows(iRow)) = 0 Then
'keep looking
Else
LastRow = .Rows(iRow).Row
Exit For
End If
Next iRow
End With

If LastRow = 0 Then
MsgBox "None used!"
Else
MsgBox "last row was row#: " & LastRow
End If

End Sub



venus wrote:

Hi,

I'm fairly good with Excel, but new to vba (I barely know what I'm
doing). I have a named range of cells c6:p53, named Hours. There is
data all around this range. I want to find the last used row within
this range and have had no luck.

Almost every solution I've found just gets me the very last row of the
worksheet instead of the range. There is a thread with a solution from
Jim Thomlinson, but I get an "invalid qualifier" error when I use it. I
have looked for days on the net and when I find code that should work,
it doesn't. What's wrong with me and my pc?

Someone, please help me!

--

Dave Peterson

Find last used row in named range with data all around
 

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



venus wrote:
Hi,

I'm fairly good with Excel, but new to vba (I barely know what I'm
doing). I have a named range of cells c6:p53, named Hours. There is
data all around this range. I want to find the last used row within
this range and have had no luck.

Almost every solution I've found just gets me the very last row of the
worksheet instead of the range. There is a thread with a solution from
Jim Thomlinson, but I get an "invalid qualifier" error when I use it. I
have looked for days on the net and when I find code that should work,
it doesn't. What's wrong with me and my pc?

Someone, please help me!

Find last used row in named range with data all around
 

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

venus wrote:
Hi,

I'm fairly good with Excel, but new to vba (I barely know what I'm
doing). I have a named range of cells c6:p53, named Hours. There is
data all around this range. I want to find the last used row within
this range and have had no luck.

Almost every solution I've found just gets me the very last row of the
worksheet instead of the range. There is a thread with a solution from
Jim Thomlinson, but I get an "invalid qualifier" error when I use it. I
have looked for days on the net and when I find code that should work,
it doesn't. What's wrong with me and my pc?

Someone, please help me!

--

Dave Peterson

Find last used row in named range with data all around
 

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

Find last used row in named range with data all around
 

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

Find last used row in named range with data all around
 

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

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