VBA Excel 2010 - Determine the last used row

Dear Experts,

I have two ways in VBA which used to apply to determine the last row on the sheet, both ones of course got from EE :-)

1) LastRowWithValue = Columns(1).SpecialCells(xlCellTypeBlanks)(1).row - 1

2) LastRowWithValue = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count

Basically the second one is good for almost each cases, except one file where the first one is more reliable, but sometimes brings "No cells found" error message.

Could you please advise how it could be done that if the 1) version says "No cells found" error message, in that case the macro should apply the 2) version?

thanks,
LVL 1
csehzIT consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arno KosterCommented:
you could use something like

[...]
on error goto try_different_tactic
LastRowWithValue = Columns(1).SpecialCells(xlCellTypeBlanks)(1).row - 1
on error goto 0

[...]

exit sub

try_different_tactic:
LastRowWithValue = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count
resume next

end sub

Open in new window


or,

try something like

LastRowWithValue = range("A1").end(xldown).row

Open in new window


pay attention that this finds the first empty cell underneath A1, so this only works for lists that do not contain empty cells.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arno KosterCommented:
The difference in between 1) and 2) is also that

1) finds the last used cell in the specific first column / range("A:A")

2) finds the last used cell in any column in the worksheet, even if this is higher than then last used cell in the specific first column / range("A:A")
0
csehzIT consultantAuthor Commented:
Akoster thanks very much, sorry I am not sure where the on error go to 0 and exit sub should be?

Basically what is there function? For my targets so these would be in the first part of the code, currently applied like in the attached Code section, finally this is working just would not like to understand what I missed :-)

thanks,
Dim LastRowWithValue As Long

On Error GoTo try_different_tactic

LastRowWithValue = Columns(1).SpecialCells(xlCellTypeBlanks)(1).row - 1

try_different_tactic:
LastRowWithValue = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count
Resume Next

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Arno KosterCommented:
the subroutine setup should be like this :

sub name_of_the_routine(arguments)
  on error goto label
  [contents of normal flow]
  exit sub

label:
  [error handler code]
end sub

the position of the on error goto ... line should ideally be the first executed line of code, but can be placed just before the execution of the suspected statement as well.
all statements after the 'on error goto ...' line are executed normally until an error is thrown or the exit sub statement is reached.

in the example you give in your last comment, even when no errors are generated, the lastrowwithvalue variable will be filled with a value according to the usedrange approach.
0
Arno KosterCommented:
for more insight, you might want to try out these examples :
Sub test1()
    MsgBox "1 : visible"
    Exit Sub
    MsgBox "2 : hidden"
End Sub

Sub test2()
    On Error GoTo handler
    MsgBox "1 : visible"
    MsgBox 1 / 0
    Exit Sub
handler:
    MsgBox "2 : visible"
End Sub

Sub test3()
    On Error GoTo handler
    MsgBox "1 : visible"
    MsgBox 1 / 1
    Exit Sub
handler:
    MsgBox "2 : hidden"
End Sub

Sub test4()
    On Error GoTo handler
    MsgBox "1 : visible"
    MsgBox 1 / 1
handler:
    MsgBox "2 : visible"
End Sub

Open in new window



0
csehzIT consultantAuthor Commented:
Akoster thanks very much, yes these examples were very needed for me as never understood exactly the GoTo working
0
Arno KosterCommented:
you're welcome !

if you need any more help, feel free to post a new discussion...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.