• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

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,
0
csehz
Asked:
csehz
  • 5
  • 2
2 Solutions
 
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
 
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
 
csehzAuthor 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
csehzAuthor 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now