Link to home
Create AccountLog in
Avatar of Jagwarman

asked on

End Down problem

in some of my files where I use a macro to get a file, I need to find the last cell in a row so  I use the End Down function and then off set, which normally works find, however, if I bring in a file and there is only the header row and no additional data using End Down + off set takes me to the bottom of the worksheet and then throws out an error.

Can someone provide a simple VBA code that will overcome this problem.

Thanks in advance.
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Something like this should do the same:
ActiveSheet.Cells(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count, 1).Activate

Open in new window

Start from the bottom in a column that is always populated, and go up.  This works in all versions of Excel:

Dim LastRow As Long

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
End With

Open in new window

Avatar of redmondb
Flag of Afghanistan image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
FWIW, UsedRange is not entirely reliable as it can sometimes still refer to cells beyond the actual used range (even if you save, etc).
Avatar of Jagwarman



I am probably doing something wrong but, I have tried out your solution but it does not appear to do anything ???
Hi Redmond,

rorya is correct,  if there were other cells that were populated and these and cleared the curser still ends on the last cell that used to be there.

Without seeing how you are actually trying to implement my suggestion, it is hard for me to say what is wrong.

What my code snippet will do is populate the variable LastRow with the row number of the last populated cell in Column A of the ActiveSheet.

It is a standard technique used by many of the Experts here and at other forums.

Rorya and Jagwarman.

If anyone can provide one, I'd love an example where my code doesn't work!

My code happily handles all the following, but sometimes UsedRange gets a bad press because people don't realise that...
   - If the first x Rows/Columns are blank then they aren't included.
   - If the beginning/end of that actual used cells are hidden (or filtered) then they're not included in UsedRange.
    - While UsedRange is sensitive to  "Previously used" cells, my code automatically resets the last cell.

Besides handling all of the above, my code also copes with different length columns and unpopulated cells. Is there anything else that matches this?

Usedrange does not always work to reset the real used range, which is the point I was making. It works most of the time but not always.

My preference is to use Find generally or end(xlup) if I know the location and state of the data of interest.

Usedrange does not always work to reset the real used range, which is the point I was making. It works most of the time but not always.
Have you any feeling for when this might happen?

My preference is to use Find generally or end(xlup) if I know the location and state of the data of interest.
For those (limited!) cases where you don't know the longest column, and/or where cells may not be populated, how do you find the first unused row?

I use a function like this:
Public Function LastRowInSheet(wks As Worksheet) As Long
' Returns the number of the last row with data anywhere in it
   LastRowInSheet = 1
   On Error Resume Next
   With wks.UsedRange
      LastRowInSheet = .Cells.Find(what:="*", After:=.Cells(1), LookIn:=xlFormulas, _
                                   SearchOrder:=xlByRows, _
   End With
End Function

Open in new window

Thanks, rorya.

And the calling macro checks that filters aren't active? :)

I notice that you use UsedRange and then it struck me that perhaps the problems you've encountered with UsedRange in the past have been when "Count" hasn't been used - and so a last cell reset hasn't been done?

The problems I have had with usedrange are that it simply has no effect, no matter what you do with it. In such circumstances, I find that deleting blank rows and columns, saving and reopening the workbook also has no effect whatsoever.

Thanks, I get the picture now. Please see the attached. In the "Format" screen I formatted some cells beyond the end of the data. Both Excel (Ctrl-End) and my code consistently see row 14 as the last row, whereas your macro goes to line 10.

On the other hand, in the "Filter" sheet, your code identifies 8 (the last visible row) whereas my code returns 10 (the actual last row).

Obviously, should you be so inclined, your code can be changed to handle filters. Mine, however will unavoidably "respect" formatted lines.

Yes, that is always the problem with any "last row" (or column) function - what do you count as being a used row?

I try (whenever I remember anyway!) to ensure that filters are removed if I'm moving data around and because I typically have no interest in anything that isn't data when I'm doing it, looking for cell contents usually works for me; but really I think you'd need both LastDataRow and LastUsedForAnythingRow functions and for the latter I think UsedRange would do me 99% of the time. :)

Yes, horses for courses and I suspect any differences between the two approaches will be encountered a lot less than 1% of the time.

Thanks, your prodding taught me a lot about UsedRange's behaviour. I still think it's consistent, but, taking your point, I don't think that many people understand its definition of a used row/column - I certainly didn't! (Just hope I do now.)

Thanks John and Rory - I learned a lot from this.